How to always Reference Cell even if deleted?
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: How to always Reference Cell even if deleted?
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    15,730
    Post Thanks / Like
    Mentioned
    28 Post(s)
    Tagged
    13 Thread(s)

    Default How to always Reference Cell even if deleted?

    How can I always reference Cell(1,1) even if that cell is deleted?
    For example I want cell(1,2) to always show what is in cell(1,1) if I delete cell(1,1) I still want cell(1,2) to show what's in cell(1,1). There will always be a cell(1,1) even if you delete the cell another cell takes it place.

    I have tried defining cell(1,1) but if I then delete cell (1,1) I get a # Ref in cell(1,2).

    This is just a example I may want cell(20,13) to refer to cell(1,1) so I cannot use a offset function.
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please Iím not perfect yet. "Memories are forever"

  2. #2
    Board Regular James006's Avatar
    Join Date
    Apr 2009
    Posts
    3,288
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    1 Thread(s)

    Default Re: How to always Reference Cell even if deleted?

    Hi,

    My Answer would be ... Indirect()

  3. #3
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    15,730
    Post Thanks / Like
    Mentioned
    28 Post(s)
    Tagged
    13 Thread(s)

    Default Re: How to always Reference Cell even if deleted?

    Thanks James
    Quote Originally Posted by James006 View Post
    Hi,

    My Answer would be ... Indirect()
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please Iím not perfect yet. "Memories are forever"

  4. #4
    Board Regular James006's Avatar
    Join Date
    Apr 2009
    Posts
    3,288
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    1 Thread(s)

    Default Re: How to always Reference Cell even if deleted?

    Quote Originally Posted by My Aswer Is This View Post
    Thanks James
    You are welcome ...

  5. #5
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    85,113
    Post Thanks / Like
    Mentioned
    44 Post(s)
    Tagged
    9 Thread(s)

    Default Re: How to always Reference Cell even if deleted?

    Non-volatile:

    =INDEX(A:A,1)

    Add the sheet prefix if necessary.
    Assuming too much and qualifying too much are two faces of the same problem.

  6. #6
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    15,730
    Post Thanks / Like
    Mentioned
    28 Post(s)
    Tagged
    13 Thread(s)

    Default Re: How to always Reference Cell even if deleted?

    Thank you for your help Aladin. I was having a brain freeze. I also discovered =OFFSET(B1,,-1) would work also.
    Quote Originally Posted by Aladin Akyurek View Post
    Non-volatile:

    =INDEX(A:A,1)

    Add the sheet prefix if necessary.
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please Iím not perfect yet. "Memories are forever"

  7. #7
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    85,113
    Post Thanks / Like
    Mentioned
    44 Post(s)
    Tagged
    9 Thread(s)

    Default Re: How to always Reference Cell even if deleted?

    Quote Originally Posted by My Aswer Is This View Post
    Thank you for your help Aladin. I was having a brain freeze. I also discovered =OFFSET(B1,,-1) would work also.

    INDIREC("A1") survives any deletion.

    INDEX(A:A,1) cannot survive deletion of column A.

    OFFSET(B1,,-1) does not survive column deletion or row deletion.

    Both INDIRECT and OFFSET are volatile.
    Assuming too much and qualifying too much are two faces of the same problem.

  8. #8
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    15,730
    Post Thanks / Like
    Mentioned
    28 Post(s)
    Tagged
    13 Thread(s)

    Default Re: How to always Reference Cell even if deleted?

    Thanks Aladin
    Quote Originally Posted by Aladin Akyurek View Post
    INDIREC("A1") survives any deletion.

    INDEX(A:A,1) cannot survive deletion of column A.

    OFFSET(B1,,-1) does not survive column deletion or row deletion.

    Both INDIRECT and OFFSET are volatile.
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please Iím not perfect yet. "Memories are forever"

  9. #9
    Board Regular James006's Avatar
    Join Date
    Apr 2009
    Posts
    3,288
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    1 Thread(s)

    Default Re: How to always Reference Cell even if deleted?

    Thanks Aladin for the clarification ...

  10. #10
    MrExcel MVP mikerickson's Avatar
    Join Date
    Jan 2007
    Location
    Davis CA
    Posts
    22,518
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    15 Thread(s)

    Default Re: How to always Reference Cell even if deleted?

    How about
    =INDEX(Sheet1!1:1048576,1,1)

    It will survive cell, row and column deletion and insertion.

    Although its precedent range is all of sheet1, it is not volatile. It will not recalculate if a cell off Sheet1 is changed, as INDIRECT would.

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
  •