Using the value in a cell (which is a spreadsheet name) as part of a formula

[gheyman]

If I have in cell A1 this value [CLIN 0010 MATERIAL COST SUMMARY A3382276.xlsx]


can I write a dynamic formula that uses this value? Sort of using a concatenate

This is my formula. But I want to change it so that the part that says [CLIN 0002 MATERIAL COST SUMMARY B53388.xlsx] is dynamic and changes based on whatever I put in cell A1

=HLOOKUP((T$1&" "&$R4),'[CLIN 0002 MATERIAL COST SUMMARY B53388.xlsx]Detail'!$Q$1:$EC$14,VLOOKUP($G4,$AB$5:$AC$15,2,FALSE),FALSE)

I know this doesn't work, but something like

=HLOOKUP((T$1&" "&$R4),"'"&A1&"Detail'!$Q$1:$EC$14",VLOOKUP($G4,$AB$5:$AC$15,2,FALSE),FALSE)

 

[Fluff]

How about
=HLOOKUP((T$1&" "&$R4),INDIRECT("'"&A1&"Detail'!$a$1:$EC$14"),VLOOKUP($G4,$AB$5:$AC$15,2,FALSE),FALSE)

 

[gheyman]

Thank you!

 

[Fluff]

You're welcome.
One thing I forgot to mention, Indirect only works if the other workbook is open.