Automating update link to another workbook

So you are basically trying to use the INDIRECT functionality (incorrectly) but indirect will not work with external closed workbooks.

To learn more about indirect:
http://office.microsoft.com/en-us/ex...005209139.aspx

But like I said, you cant use indirect with a closed workbook so you will have to come up with another solution.

Right off the top of my head you have a couple of alternative options
1) You can simply open both files, find and replace the old date with the new date, then refresh links - this requires the least development time and would only work if you are doing it and not handing this tool off to someone else
2) You can have an import macro that you use to simply copy over the data you need into a background tab then link your report to the new tab. -- This is probably what I would do as you can either have a cell reference the name of the file you want to open and copy from, or you can allow the macro to choose the workbook you want to use to open and copy from.
3) You could use the Pull UserDefinedFunction as indicated here: http://numbermonger.com/2012/02/11/e...sed-workbooks/
I have tried it just to test that it works but I ended up deciding on option 2 above as it was the easiest for everyone else to understand (since I was handing the tool over to someone else)