SOLVED... didn't need a macro!

[Closed]
Report
-
Posts
2809
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 23, 2021
-
Hello,

Turns out no macro is needed... =INDEX(Sheet1!B:B,MATCH(A1,Sheet1!A:A,0))

I have a rather complex issue I'm trying to solve with a macro. Any help would be greatly appreciated. First, let me give you a description of the workbook setup.

The first sheet has two columns. Column 1 is a list of account numbers (several thousand) and Column 2 is the respective value of each acount (in dollars). The problem is that the accounts aren't static, as new accounts are opened/closed daily so using a direct cell link isn't working.

After the master worksheet of all accounts/values, there is a worksheet within the same book for each customer (several hundred). The account numbers are manually put in for each customer on their respective worksheets.

What I am trying to accomplish is some sort of macro that will update each customer's account balances in their respective sheets. For example, the second sheet may have 10 accounts listed in Column 1.

What I want it to do is when I update sheet 1 daily by copying and pasting the master list of accounts/values, each customer sheet will search for the account number listed in that sheet on the master sheet, and when it finds the account number in Column 1 of the master sheet it will update it with the value in Column 2 from that master sheet in the individual sheet.

Lets say this is sheet 1:
Acct Balance
1 $20
2 $50
3 $34
4 $17
5 $27
8 $12
13 $62

Those numbers won't remain static, as if account number 6 is created it would move account 8 from A6 to A7.

On sheet 2, lets say it is like this:
Acct Balance
1
3
8

When the accounts/values in sheet 1 are updated daily, I want to be able to run a macro that would search for accounts 1, 3, and 8 and update sheet 2 with those daily values. This is a very small example, but if someone could help me make this work it would be awesome! Thanks in advance!

1 reply

Posts
2809
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 23, 2021
484
Hi Mike,

Sounds like VLOOKUP could work for you.

Sheet2, B2 formula: =VLOOKUP(A2,Sheet1!$A$2:$B$8,2)
Adjust the range of sheet1 and drag formula down.

This will work as long as the account numbers are in order.

Best regards,
Trowa