Creating workbook references based on a list of filenames

Copper Contributor

I have a list of files from which I would like to extract data - data is in the same location in each of these files.

I want to write a formula (and I know that INDIRECT only works with open workbooks, so that wouldn't work) that references those filenames and returns the value in the referenced or linked workbook.

I know that I can type '=' and then click over to the workbook in question, but I have lots of them to do, and am looking for a better way.

Any suggestions would be helpful.

1 Reply



As you said with formula the file should be open.
Here is a small formula example:

=SUMPRODUCT('C:\Documents and Settings\Owner\Own Documents\WorkFiles\Customer\Evaluations\["&$A$1&G$3&".xls]NewCustom_Active'!AC1)


But you could use VBA to read data from a closed workbook.

How To Import / Copy Data From Closed Workbook Into Current Workbook?

Standard disclaimer: none Microsoft site.




If I may also recommend, always add your Excel version, operating system, storage medium and other necessary information on your topic as information.

This way you will get a solution proposal much faster and more accurately.


Hope I could help you with these information / links.



I know I don't know anything (Socrates)