CODEWITHSUNDEEP
excelexcel-formulafilenames
Orknie
Orknie

Reputation: 97

How do you integrate a filename reference into a formula?

I'm using HLOOKUP() and need to reference a filename within the HLOOKUP(). Because the filenames change from daily(3) to daily(4) ... daily(200), I'm using CONCATENATE() to change the filename to 3, 4, ... 200. 

=HLOOKUP(E$1,'daily(3).csv'!$1:$2,2,FALSE)

=CONCATENATE("'daily(",A428,").csv'!$1:$2")

'daily(3).csv'!$1:$2

How can I integrate the cell reference within the HLOOKUP()?

Upvotes: 0

Views: 135

Answers (1)

Luuklag
Luuklag

Reputation: 3914

Use the Indirect option. So it would become =HLOOKUP(E$1,INDIRECT(CELL),2,FALSE) where CELL is a reference to the cell that your CONCATENATE function is in.

Also see: http://www.mrexcel.com/forum/excel-questions/684642-use-text-cell-part-reference-formula.html

And: https://support.office.com/en-us/article/INDIRECT-function-474b3a3a-8a26-4f44-b491-92b6306fa261

Putting it all together in a single formula:

=HLOOKUP(E$1,INDIRECT(CONCATENATE("'daily(",A428,").csv'!$1:$2")),2,FALSE)

Upvotes: 2

Related Questions