Forum Discussion
YR26_7
Feb 19, 2024Copper Contributor
Is it possible to use a cell value for a workbook reference?
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 ...
- Feb 19, 2024
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")
YR26_7
Feb 19, 2024Copper Contributor
Thank you! Is there any workaround to needing the workbook open, or is that a different beast?
Maciej_Kopczynski
Feb 19, 2024Brass Contributor
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.