Search in Excel File

[Tony]

I have few worksheets in a workbook. I try to write a formular to lookup a
value from a worksheet named "Open-3035". The formula will look like "
=HLOOKUP($B$1,'Open-3035'!$C$4:$N$28,25,FALSE) "

However, my formula has to change the worksheet's name sometime. Therefore
I try to modify the formula by change the name of the worksheet into a cell
"A5" then the formula changed to
"=HLOOKUP($B$1,'Open-A5'!$C$4:$N$28,25,FALSE)"

in cell A5 which content a value "3088", I expect the new formula will then
like =HLOOKUP($B$1,'Open-3088'!$C$4:$N$28,25,FALSE), however, the formula
doesn't work. Could anyone help me. Thanks a lot.

 

[JE McGimpsey]

I have few worksheets in a workbook. I try to write a formular to lookup a
value from a worksheet named "Open-3035". The formula will look like "
=HLOOKUP($B$1,'Open-3035'!$C$4:$N$28,25,FALSE) "

However, my formula has to change the worksheet's name sometime. Therefore
I try to modify the formula by change the name of the worksheet into a cell
"A5" then the formula changed to
"=HLOOKUP($B$1,'Open-A5'!$C$4:$N$28,25,FALSE)"

in cell A5 which content a value "3088", I expect the new formula will then
like =HLOOKUP($B$1,'Open-3088'!$C$4:$N$28,25,FALSE), however, the formula
doesn't work. Could anyone help me. Thanks a lot.

One way:

=HLOOKUP($B$1,INDIRECT("'Open-" & A5 & "'!C4:N28"),25,FALSE)