Referencing closed workbooks in an INDIRECT like fashion

[nigeli]

I have a folder of documents that are excel sheets that have been filled out and named various things. The data in those sheets is not organized as a spreadsheet, it's more like a form that people fill out. As a result I want to create a central spreadsheet that will organize all the data. I'd rather not copy/paste each form into the master.

What I was thinking was that I would use INDIRECT for this. On the forms the various pieces of data I want to grab are always in the same cells on the same sheets, although the names of the workbooks vary. I thought I would create a column for each piece of data I want from the form, then in each column I would write a formula that referenced the first column, which would contain the filename to look in.

So something like this.

File Name	Data Point 1
Form1.xlsx	=indirect("'C:\Documents\Temp Docs\["& A2 &"]Worksheet'!$B$2")

With the idea being that reference looks at the first column, retrieves the name of the file and populates the cell with the data from the cell in the other file. I could then have a large number of cells, all of which reference other cells in the documents, and have an easy way of updating whenever a new document was added.

Unfortunately this doesn't work (I get #ref errors), and looking around the internet it looks like INDIRECT will not work with closed workbooks. I know this could be better solved through developing an access database, but I'm restricted to excel right now for non-technical reasons. Is there a way to do this in excel? My issue is mainly that a large number of these forms get filled out and added to a folder, and I'd rather avoid manual solutions (such as updating each formula by hand or copy the sheets into a central workbook).

[Glenn Kennedy]

Can you use (free) 3rd party software? if so, Morefunc contains the very useful INDIRECT.EXT which works on closed files. It works with Excel 2010, too. But there's a fix you have to apply. If you CAN use 3P stuff, I'll find the URLs for you.

[nigeli]

Looks like I can't without heavy difficulty (corporate environment, several layers of approval, etc.) so if it can be solved through formulas or VBA that would be a lot easier. At this point I'm just considering copying all the formulas and using a Replace (not a formula, really doing a find and replace) with the proper filenames. It's annoying and manual but would work. I just feel like there must be something better.

[Glenn Kennedy]

only several layers... you're lucky!!