Copper Contributor

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.

3 Replies


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:


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: