Telling excel that a value in a cell is the sheet name

drater

New Member
Joined
Dec 12, 2005
Messages
5
Hi everyone,

In excel, I have a list sheet names in a collumn B. To the right of collumn B, I want to do a vlookup to retrieve values from those sheets in collumn B. However, instead of manually entering the sheet name in the vlookup formula, I would just like the value in collumn B to fill in the sheet name in the formula.

For example, instead of saying Cat!c5, I would like to say b3!c5, where b3 says the word cat in the sheet I am currently in.

Thanks for your help.
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

drater

New Member
Joined
Dec 12, 2005
Messages
5
Thanks. I have gotten it to work when linking to a sheet within the workbook. What if I want to link to a sheet in another workbook. I get a #NAME? error which I believe is from the filename of the other workbook.
 

drater

New Member
Joined
Dec 12, 2005
Messages
5
This is what I typed in:

=vlookup(c2,[Reports.xls]indirect(d32&"!$A$36:$A$41"),18)
 

drater

New Member
Joined
Dec 12, 2005
Messages
5
=VLOOKUP(C2,INDIRECT("'[Reports.xls]"&D32&"'!$a$36:$r$41"),18)


Is the statement written correctly? I changed it but still get an error message.

Thanks.
 

Watch MrExcel Video

Forum statistics

Threads
1,119,128
Messages
5,576,253
Members
412,709
Latest member
Rishu
Top