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
Hassan
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:
{12,"Sheet1";13,"Sheet2"}
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.
Aladin
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
http://www.asaputilities.com/
