Data source names changes daily (by date) can I have a formula what will pull newest file data?

[rmitnick]

Hello:
I have a daily report that pulls from external data sources (other excel files created by other users). Each day a new file is created, the naming convention is the same except for the date. An example would be : Consolidated Cash Report Summary AR100233D 2014 0319v.xlsx Tomorrow the only change will be 2014 0320v towards the end. The date will always be today's date. is there a way, through concatenate or other method to pull data daily from a different dated source?

Full source path example:
='T:\Performance & Analytics\Analyst\03.0 Client - LOB\BVD\John Smith\00 - Consolidated Cash Reports\[LHP Consolidated Cash Report Summary AR100233D 2014 0319v.xlsx]Facility Summary'!$Q$14

I would prefer to NOT have to modify the date portion of the file name daily. The cells/tab names I reference don't change.

 

[Andrew Poulsom]

You can use the INDIRECT function, but the source workbook must be open.

 

[rmitnick]

You can use the INDIRECT function, but the source workbook must be open.

Thank you, Andrew. I was trying to avoid having the file open since I'm pulling from multiple sources (all similarly named). but if this is the best I can do, it's better than what I am doing now.