For several years I have used excel to generate a check register. Each year is a workbook and each sheet is a month. In order to make comparisons I keep the Month & year to date figures for 4 successive years in each sheet. This is accomplished with cut & paste links ( IE: 'C:\Documents and Settings\My Documents\.....\[Checklog2003.xls]March'!$L9 ). As the sheets have become more complicated over the years the number of links have grown to almost 1500. Each year the links must be adjusted to move the history forward a year. While I can use search & replace to cut down the time, a more elegant solution would be to make the links dynamic ( IE 'C:\Documents and settings\My Documents\.....\[Checklog' & =YEAR(A1)-1 & '.xls]March'!$L9 ). While I can build the string correctly, Excel no longer treats it as a link (#REF). I am aware of the INDIRECT function, however having to open all the other sheets each time I wish to make an entry defeats the purpose of the sheet. I can think of several applications that could use this type of dynamic linking. My question, does anyone know a way around this dilemma Thanks...

Welcome to the board!

Can you give a little more on the set up. After reading it, it seems that there is too much redundant data, and a re-design might be more effective, that needs to be updated only in one place.

Can you attach a small sample, and how you want it to be?

Hi

Instead of search/replace why not use the Edit>Link>Change Source menue option to selectivly change the link source(s). This will change all the link from a given workbook to the new work book you select.

Would that do it for you?

MTB