Hlookup using Sheet Name lookup

[erniepoe]

Hi all,

I have a summary sheet and about 250 different worksheets, and in column C of the summary page I have the name of each tab in the workbook.

I would like to do a lookup to the particular worksheet, and once there, find the word "total" in Row 8 (will be found no later than column N, but sometimes as early as column F), and then Hlookup off that "total" to provide the value listed 13 rows down.

Is this something that is possible? Thanks in advance for any help.

 

[Jonmo1]

Try

=HLOOKUP("total",INDIRECT("'"&LOOKUP(yoursheetnamelookup)&"'!F8:N21"),14,0)

 

[erniepoe]

thank you Jon. Where you write "yoursheetnamelookup", do i just substitute cell C7 (the sheet name column) and then just drag it down accordingly?

 

[Jonmo1]

I thought you were using a lookup function to find the sheet name.
Anyway, Yes. Replace LOOKUP(yoursheetnamelookup) with C7
So
=HLOOKUP("total",INDIRECT("'"&C7&"'!F8:N21"),14,0)

 

[erniepoe]

ah, that worked perfectly! Thank you Jon!

 

[Jonmo1]

You’re welcome