MrExcel Publishing
Your One Stop for Excel Tips & Solutions

reverse vlookup

Posted by hassan omar on May 24, 2001 7:06 AM

I need to find a way to get the value in cell c49 of every worksheet in a workbook onto one main worksheet that will list those values. On the main worksheet I have 200 account numbers, 1 for each worksheet in the book. On every worksheet cell b49 is the account number and c49 is the account balance. is there a simple way to do this without using vba, or if vba is the only way, how to set it up.

Posted by Aladin Akyurek on May 24, 2001 7:58 AM


You have apparently all account numbers listed on the main sheet.
I'd suggest to list sheet names next to each account number. Consider for example the following data:


occupying the range A1:B2. The numbers stand for account numbers and names for sheets where they can be found. (In your case, this would become A1:B200).

I'd use the followin formula to collect the balance associated with an account number:

in C1 enter: =INDIRECT(B1&"!"&"C49")

Copy down this for all account numbers.


Posted by hassan omar on May 24, 2001 11:25 AM

That would be practical if the sheets were named sheet1, sheet2, etc..
However, each sheet has a unique name

Posted by hassan omar on May 24, 2001 11:28 AM

Posted by Aladin Akyurek on May 24, 2001 11:30 AM

Attention Macro Wizards -- Hasan needs one that collects all sheet names of a wb in one place! (NT)

Posted by IML on May 24, 2001 2:17 PM

I'm a macro wizard!

Yeah, right.

If you down load this utility, it is option from
ASAP Utilities -- Information -- List all sheet names.
It opens a new file, but you could cut and paste it to your sheet.

good luck