Collecting data from multiple closed excel files based on criteria - New to EXCEL advanced

[zoleegee]

I'm new to Excels advanced feature so apologies if my question is a noob one.
Similar task might have been addressed already, but I couldn't find a solution to my specific issue.

I am trying to collect data from multiple files:
- All files are in the same folder
- Files share the same naming convention (report 01-07-2015.xlsx, one for each day of the month)
- The sheet I need data from are the same name (Report) and structure is also same in all files.
- From each files' 'Report' sheet, I need the values of C1,D1,E1,F1 cells, populated into different rows in the destination file, based on a reference value(A5) from the destination file.
- All sheets contains the reference value I want to refer to from my destination file.

I've tried with Vlookup, but I don't want the files to be opened... Found also a 3rd party add-on (morefunc - indirect.ext function for the file names) that would be perfect, but since I am using Excel 2013 64bit, I cannot use that.

Any suggestion would be much appreciated.
Thank you!

[Kaper]

I've tried with Vlookup, but I don't want the files to be opened

Vlookup works also with closed files. You just need to include whole path to file in address, like:

Please Login or Register  to view this content.

[Kaper]

PS. You may also like this: http://www.rondebruin.nl/win/s3/win008.htm

[zoleegee]

Vlookup works also with closed files. You just need to include whole path to file in address, like:

Please Login or Register  to view this content.

Sorry, my description is a bit misleading.
You're absolutely right, using the full path/filename is working with VLOOKUP. As I have 30-31 files (one for each day) I didn't want to manually write the full path/filename in each cell, instead I wanted to use the INDIRECT function to pull the file name from a hidden list. That function however only works if the files are open.

[Kaper]

Of course INDIRECT needs open files.

Have you seen my second post with a link to Ron's pages?

Another option - with relatively simple VBA code you can write formulas with full paths into cells

[zoleegee]

Of course INDIRECT needs open files.

Have you seen my second post with a link to Ron's pages?

Another option - with relatively simple VBA code you can write formulas with full paths into cells

I've only seen it after I wrote my answer...
I am looking into it, and will get back with the results. Thank you!