Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Illinois
    Posts
    135
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Links & Insertions (2000)

    My worksheet starts with a list of employee data. Later sheets link back (Edit, Paste Special, Link) with selected columns.
    I link so revisions will reflect globally. However, if I insert a new row, other sheets don't show this.
    I may want to link to the first sheet from other worksheets. Is there a better approach in Excel?

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Links & Insertions (2000)

    If you link with cells, and you insert rows or columns the links will "adjust" so they are pointing to the same cell you linked (this is by design).

    If you don't want this you can use an indirect funtion to link to a sheet with the same cell reference:
    Enter this in A1 and copy it to whatever cell reference get from "sheet1" (change the name as appropriate)
    =INDIRECT("'Sheet1'!"&CELL("address",A1))

    This formula will always link to the relative cell, even if rows/columns are inserted/deleted

    Steve

  3. #3
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Illinois
    Posts
    135
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Links & Insertions (2000)

    Inserting a row in my source, Sheet 1, does not give me another row on sheets that link back.

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Links & Insertions (2000)

    And they won't unless you use macros (which then negates the need to use formulas, you could just copy the cells.)

    You would need to extend the range of "linked cells" as far down as you anticipate sheet1 to grow.

    If you want the "hide" the blank cells, you could use something like this:
    =if(isblank(INDIRECT("'Sheet1'!"&CELL("address",A1 ))),"",INDIRECT("'Sheet1'!"&CELL("address",A1)))

    Steve

Posting Permissions

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