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

[drater]

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.

 

[Von Pookie]

=INDIRECT(B3&"!C5")

 

[drater]

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]

This is what I typed in:

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

 

[drater]

=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.