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

gheyman

Well-known Member
Joined
Nov 14, 2005
Messages
1,690
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
34,030
Office Version
365
Platform
Windows
How about
=HLOOKUP((T$1&" "&$R4),INDIRECT("'"&A1&"Detail'!$a$1:$EC$14"),VLOOKUP($G4,$AB$5:$AC$15,2,FALSE),FALSE)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
34,030
Office Version
365
Platform
Windows
You're welcome.
One thing I forgot to mention, Indirect only works if the other workbook is open.
 

Forum statistics

Threads
1,086,246
Messages
5,388,672
Members
402,134
Latest member
McKnze21

Some videos you may like

This Week's Hot Topics

Top