Indirect lookup

**kborgers** · 11-07-2012, 10:17 AM

Hi All,
I think I need the Indirect function for this scenario but I cannot figure out how to use it correctly:
I have a column of file names - ie. CEL43.xls, that relate to files that are all contained within the same folder. In a new workbook, I'm trying to look up a common cell (H9) in each file for summary purposes, without opening any of the files. Any suggestions welcome please!

**kborgers** · 11-07-2012, 03:14 PM

Hi People, I've had a lot of views to this question but no answers - I guess it's not possible then? Perhaps one of the 'super-brains' could just confirm? Many thanks!

**BB1972** · 11-07-2012, 03:28 PM

Hey,

I might have misunderstood your query, but... can you not just type in the filename, followed by the tab name, followed by the cell address (H9) in your new workbook? In the format '[filename.extension-name]tabname'!celladdress

**kborgers** · 11-07-2012, 03:32 PM

Hi Brendan,
Thanks for replying. The issue is, that I have a column of very many workbook file names that each need to be referred to in order for me to extract the required information. Doing it on a file by file basis would take me an awful long time. Therefore I really need to find a formula that will refer to each file name as part of the lookup function. Does this make sense?

**Pepe Le Mokko** · 11-07-2012, 03:36 PM

Originally Posted by kborgers

Hi People, I've had a lot of views to this question but no answers - I guess it's not possible then? Perhaps one of the 'super-brains' could just confirm? Many thanks!

Hi
please note that forum rules ask to wait about a day before bumping - Your problem will probably need VBA to be solved. Ask a mod to move it to the correct forum

**BB1972** · 11-07-2012, 03:43 PM

Ah, right - I see what you mean now. I'll be following this thread with interest, as that's something I tried to do recently. (I failed miserably!)

**6StringJazzer** · 11-07-2012, 03:43 PM

You can use INDIRECT to reference the cells in those files, but if you use INDIRECT, Excel requires the referenced file to be open.

There is an add-in floating around called INDIRECT.EXT that allows you to use a function similar to INDIRECT that does not require the file to be open. Here is one page that describes it.