Use a cell reference in a pasted link formula

[pfrost]

I have two workbooks. One pulls financial data from our business system. It's updated monthly, with each month in consecutive columns. At the end of the year I save the workbook to a new name for the upcoming new year and save the old workbook as a static sheet. Name is usually: Financial Pull 2005, so next year it will be Financial Pull 2006.
The second workbook, created by a different user, pulls some specific data from the first workbook. He is using pasted links and is setting it up to pull from each month as it updates through out the year. What I want to do, is to be able to reference the workbook I want to pull from, in a cell someplace in workbook 2, say $A$1, then set all the Link Formulas to refernece that cell. so a link formula which looks like =`[Financial Pull 2005]OPS Full'!$C$5 could be changed to something like =[$A$1]OPS Full!$C$5 , of course this doesn't work, but is there a way to make it work? I do not want to have to go through and change 25+ Links per month times 12 months at the start of a new year. It would be easy to change one cell reference.

Thanks for you help!

Peter

[olasa]

To pull information from closed worksheets is tricky, without some help.
This conversation might help: http://www.excelforum.com/showthread...hreadid=369332


Ola Sandström