Is it possible to use a cell value for a workbook reference?

[YR26_7]

Been churning through a project for work and I’m missing one piece.

If I have a workbook name 010203 and i link it, it displays as:

’[010203.xlsx]Sheet1’!$A$1

If I have another workbook and fill the value of a cell with:

B1 = 010203

Is it possible to recall the original workbook with:

’[ B1 .xlsx]Sheet1’!$A$1

My first reaction was to try CONCAT but no luck.

[#2 · Accepted solution]

@YR26_7 

You can use INDIRECT, but please note that it will only work if the workbook referred to is open in Excel too.

=INDIRECT("'[" & B1 & ".xlsx]Sheet1'!A1")

[#3]

Thank you! Is there any workaround to needing the workbook open, or is that a different beast?

[#4]

@YR26_7 

Not with a formula. Perhaps it's possible using PowerQuery, but I'm not an expert on that.

[#5]

Hi! You could achieve it using Power Query as @HansVogelaar mentioned. The solution would vary depending on where your files are stored because of the path - locally on your C drive, in Sharepoint or OneDrive. You would have to setup a table with path to the file where your data resides that we would use as parameter in our query. It should be pretty straightforward to build. This workaround would require occasional query refreshes (as often as your data changes in the source file). If you want to try this method and need assistance, let me know.