Use cell references in external queries

[johnb49]

When creating a link to a cell in another workbook, I want to make the query
dependent on the contents of a cell in the destination worksheet. For
example, I want to specify the path, the tab and the workbook in cells and
create references to these cells in the external link. Is this possible?

 

[Pete_UK]

You can use the INDIRECT function to do this, but it will only work
with open workbooks. As such, the path is not needed, but the filename
is. Suppose you have filename.xls in A1, Sheet Name in B1, and cell
reference (eg A42) in C1 - then your formula would be:

=INDIRECT("'["&A1&"]"&B1&"'!"&C1)

But as I say, filename.xls will have to be open for this to work.

Hope this helps.

Pete