Forum Discussion
PULLING IN DATA FROM ANOTHER FILE
I have one file that uses data that is contained in one of several different files. I specify which file I want to access in the first column of a row, such as DEAL#1 (to retrieve data from file DEAL#1.xlsm). Then I want to put a formula in a different cell that pulls in whatever is in say cell C3 of DEAL#1.
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.
- 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).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.
