Referencing part of a formula from text in another cell

[kmcrae2024]

I have a VLOOKUP that's working as expected.
The formula is =VLOOKUP($A10,[2024_TEST_File.xlsx]DATASHEET!$A:$CD,COLUMN(), FALSE)
I now have put this text into cell C10: [2024_TEXT_File_KEVIN.xlsx]DATASHEET!
I want to change the VLOOKUP formula to pull the filename from cell C10 instead of having it hardcoded in the formula.
I've tried several attempts with INDIRECT and concatenating the contents of the cell but just can't get it right.
Any help would be great. thanks

 

[awoohaw]

The formula is =VLOOKUP($A10,[2024_TEST_File.xlsx]DATASHEET!$A:$CD,COLUMN(), FALSE)
C10: [2024_TEXT_File_KEVIN.xlsx]DATASHEET!

The formula is =VLOOKUP($A10, C10 & "$A:$CD" ,COLUMN(), FALSE)


The workbook referenced in C10 MUST BE OPEN

If that doesn't work, then go one step at a time. In another worksheet in your project workbook
test the reference in C10. Just retrieve a few cells
=C10 & "A10:A20"

and build from that.

 

[Fluff]

Hi & welcome to MrExcel.
How about

=VLOOKUP($A10,INDIRECT(C10&"$A:$CD"),COLUMN(), FALSE)