+ Reply to Thread
Results 1 to 2 of 2

How do I link cells, sheet to sheet, to recognize row deletions?

  1. #1
    LeeC
    Guest

    How do I link cells, sheet to sheet, to recognize row deletions?

    In Office XP - Excel - I want to copy a column from one sheet to another
    sheet in the same workbook. I want the copied links to recognize row
    deletions and insertions when I change the source sheet. When I use the
    Copy, Paste Special, Paste Links command, the linked sheet does not recognize
    a row deletion in the source sheet. Instead, the linked sheet displays a #Ref
    message in the cell related to the deleted row.

  2. #2
    Max
    Guest

    Re: How do I link cells, sheet to sheet, to recognize row deletions?

    One way to try ..

    Assume the source col to be linked is col A in Sheet1, A1 down

    In Sheet2
    -------------
    Put in any starting cell, say A1:
    =OFFSET(INDIRECT("Sheet1!A1"),ROWS($A$1:A1)-1,)

    Copy A1 down as many rows
    as the linking is required to col A in Sheet1

    This should now "recognize" any subsequent row* insertions / deletions in
    Sheet1 (*or even column deletions in Sheet1)
    viz. col A in Sheet2 will always point to what's in col A in Sheet1

    Adapt to suit. For e.g. if you want to link to col B in Sheet3 instead,
    change:

    INDIRECT("Sheet1!A1")
    to
    INDIRECT("Sheet3!B1")

    in the formula
    --
    Rgds
    Max
    xl 97
    ---
    GMT+8, 1° 22' N 103° 45' E
    xdemechanik <at>yahoo<dot>com
    ----
    "LeeC" <[email protected]> wrote in message
    news:[email protected]...
    > In Office XP - Excel - I want to copy a column from one sheet to another
    > sheet in the same workbook. I want the copied links to recognize row
    > deletions and insertions when I change the source sheet. When I use the
    > Copy, Paste Special, Paste Links command, the linked sheet does not

    recognize
    > a row deletion in the source sheet. Instead, the linked sheet displays a

    #Ref
    > message in the cell related to the deleted row.




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1