Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Making rows constant links when inserting

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I have data on sheet1 linked to sheet2. I need to keep links constant whenever a blank row needs to be inserted. I've tried $A$4, and I've tried naming the cells. What I need is to keep a link on two rows no matter how many lines are inserted.

  2. #2
    MrExcel MVP Russell Hauf's Avatar
    Join Date
    Feb 2002
    Location
    Portland, OR Area - USA
    Posts
    1,607
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-11 11:17, emissivity wrote:
    I have data on sheet1 linked to sheet2. I need to keep links constant whenever a blank row needs to be inserted. I've tried $A$4, and I've tried naming the cells. What I need is to keep a link on two rows no matter how many lines are inserted.
    If you want your formula(s) to always refer to a certain cell, for example, B2, then you could use the INDIRECT function.

    For example,

    =INDIRECT("Sheet1!B2")

    Will always refer to cell B2 on Sheet1, no matter where you insert/delete rows. But be careful, because it will also always refer to Sheet1, even if you rename Sheet1 (you will get an error - #REF!).

    Does this help?

    -Russell

  3. #3
    New Member
    Join Date
    Mar 2002
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thank-you very much, this is exactly what I was trying to do!

  4. #4
    Board Regular
    Join Date
    Jun 2009
    Location
    Sydney, ex Johannesburg
    Posts
    54
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Making rows constant links when inserting

    Hi Russell
    I had the same problem, so I also used =INDIRECT("Sheet1!B2"). However, when I try to copy the formula down the rows, the value of B2 doesn’t increase.
    ie I want =INDIRECT("Sheet1!B2") to change to =INDIRECT("Sheet1!B3"), B4 etc
    How do I do this?

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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