Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2004
    Posts
    1

    Unanswered: Making Excel Links Dynamic

    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...

  2. #2
    Join Date
    Oct 2003
    Posts
    1,091
    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?
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  3. #3
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •