Variable, INDIRECT function help

[lojkyelo]

I have a sheet, that pulls values from two workbooks to do a comparison. I'd like to make it more flexible, so I believe I need to use the INDIRECT function to set the filename as a varaible.

Attempts to date, in C1 a file path A:\[file.xlsx]Sheet1 in A3 =INDIRECT("'"&C1&"'!B2") and various like combinations to substring the path.

In A3 what the result should be is ='A:\[file.xlsx]Sheet1'!B2 which does behave as expected when I enter it directly.

Any help be appreciated.

 

[PCL]

If you want in A3 to have
'A:\[file.xlsx]Sheet1'!B2

just put

="'"&C1&"'!B2"

 

[lojkyelo]

I want the corresponding value on the file.xslx sheet in B2 - not the literal path. Sorry, hard to describe. On the file.xlsx sheet in B2 is a string I want to use in a comparison.

 

[Aladin Akyurek]

In A3

=INDIRECT("'"&C1&"'!B2")

should return the value of B2 from the file C1 points if the file in question is open.

 

[lojkyelo]

Good call, the file is shared and was locked. Works as desired, probably need to go the version 2.0 and write a little VBA to open the file and set the file path now... Thanks!

 

[Aladin Akyurek]

Good call, the file is shared and was locked. Works as desired, probably need to go the version 2.0 and write a little VBA to open the file and set the file path now... Thanks!

You are welcome.