PULLING IN DATA FROM ANOTHER FILE

[WittyGuy55]

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.

[#2]

@WittyGuy55 

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.

[#3]

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).

[#4]

@WittyGuy55 

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.