A lookup formula to pickup filename from a different cell without opening source files.

I have about 300 identically structured workbooks (don't ask!) each with one worksheet called "Sheet1" listing points held by people. Each has a list of dates in column A, movements of points (plus and minus) in columns B and C and a balance in column D. On a new workbook, I want to mine all these individual workbooks to get a list of balances as at a specific date - say 31 March 2012.

All the files are in the folder C:\Points4G\

Using a rename program I can easily rename all the workbooks and I have renamed them P4G0001, P4G0002, P4G0003 and so on. In column A of the new workbook, starting at row 2, I have a list of numbers 0001, 0002, 0003 and so on. I then use this formula:

=VLOOKUP($D$5,INDIRECT("'C:\Points4G\[P4G"&A2&".xls]Sheet1'!$A$2:$D$1000"),4)

Cell D5 contains the date = 31/03/2012. I can copy this formula down all rows on the new worksheet.

This works fine but ONLY if the individual workbooks are open. With about 300 workbooks this is very time consuming. On another thread I learned about using INDIRECT.EXT (downloadable with other functions at http://download.cnet.com/Morefunc/30...-10423159.html) which works with unopened workbooks. However, I find that whilst I can get this to work without opening the source file with some functions I cannot make it work with LOOKUP functions.

Thus =VLOOKUP($D$5,INDIRECT.EXT("'C:\Points4G\[P4G"&A2&".xls]Sheet1'!$A$2:$D$1000"),4) works but I still have to open the individual workbooks.

Anyone got any ideas how I can do this without having to open 300 workbooks!

Thanks

Graham

Hi Pepe

Thanks for that - it's a useful Add-in to know about. I tried it out but it doesn't really help me in this situation as it dumps all the data on one sheet. A balance is not struck every day so that the balance outstanding as at 31 March won't necessarily be dated ON 31st. If it was I could then use SUMIF or something to get a total. As it is I can use various sorts to get an approximation but not an accurate answer. In any event I was looking for something that will give me a list of balances.

But thank you anyway for your help which is much appreciated.