Forum Discussion
HansVogelaar
MVP
If you can keep the other workbooks open, you can use the INDIRECT function:
=INDIRECT("[" & $A2 & "]SheetName'!C3")
where SheetName is the name of the sheet you want to get data from. INDIRECT doesn't work with closed workbooks though.
WittyGuy55
Sep 22, 2023Copper Contributor
Thanks Hans. I managed to get it to work with the following formula:
=INDIRECT("'C:\EXCEL\ABW\["&$A7&".xlsm]FINANCES'!$D3")
As you mentioned, the drawback is that all the files that I want to "pull from" have to be open.
Do you have any alternative that doesn't require the files to be open?
David Hill (in Florida).
=INDIRECT("'C:\EXCEL\ABW\["&$A7&".xlsm]FINANCES'!$D3")
As you mentioned, the drawback is that all the files that I want to "pull from" have to be open.
Do you have any alternative that doesn't require the files to be open?
David Hill (in Florida).
- HansVogelaarSep 22, 2023MVP
There are several options:
- Power Query - see for example 07 - Import Data from an Another Excel Workbook using Power Query
- VBA - see for example Wise Owl Answers - How do I get data from multiple closed Excel files using VBA?
- Create worksheets in the current workbook with formulas that link to the closed workbooks. Name the sheets after the workbooks. You can then use INDIRECT.