Lookup Via sheet Name

[rpaulson]

here is my formula.

=HLOOKUP(A1,'[TimeCard Summary (Responses) (1).xlsx]Form Responses 1'!$1:$2,2,0)

it works, but I would like to change the sheet name "Form Responses 1" to a cell referance.



ex: if cell B1 = Form Responses 1
=HLOOKUP(A1,'[TimeCard Summary (Responses) (1).xlsx]B1!$1:$2,2,0)

thanks,

Ross

 

[Jonmo1]

You need Indirect for that.

=HLOOKUP(A1,INDIRECT("'[TimeCard Summary (Responses) (1).xlsx]" & B1 & "'!$1:$2"),2,0)

Note, indirect will not work if that external workbook is closed.

 

[rpaulson]

Works,

Txs

 

[Jonmo1]

You're welcome.

 

[TimNevins]

Note in particular the triple quotes (a single and a double together). In both instances the single quote is on the right. It's not as obvious on my screen.