File route with variable

[Michael_EVB]

Hello,

Is there a way to insert a variable into a route file?

I’m using index function with match function to find values from a different file.
Although the data needs to be searched in different worksheets depending on the value of a cell.

Is there a way to specify with a cell in which worksheet it is supposed to look for the data?

[#2]

@Michael_EVB You can try INDIRECT, but it requires the source workbook to be open. If you'll be working with closed workbooks, you can download the MoreFunc add-in and use INDIRECT.EXT.

[#3]

@Smitty Smith thank you for your response.

Could you give me a short example of the function so I can better understand it?

[#4 · Accepted solution]

@Michael_EVB Here you go. In cell A1, I have a reference to an external workbook that's open. In cell B1, I have INDIRECT("'"&A1), which returns the value in the external workbook. If you close the source workbook, then the function will return a #REF! error, which is why you need to use INDIRECT.EXT from the add-in. If the source workbook will always be open alongside the destination, then no problem.

The "'" part of the formula is to add back the apostrophe that should be at the beginning of the file path. It is there in cell A1, but Excel sees it as a non-printing character.