+ Reply to Thread
Results 1 to 2 of 2

Make a referenced cell stick with the original cell coordinates when the source is moved

  1. #1
    Registered User
    Join Date
    08-27-2012
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    1

    Question Make a referenced cell stick with the original cell coordinates when the source is moved

    I have a list on "Page A" where cells A1-A6 contain

    "One"
    (empty row)
    "Two"
    (empty row)
    "Three"
    (empty row)

    On "Page B", I have referenced cells

    ='Page A'!A1
    ='Page A'!A2
    ='Page A'!A3
    ='Page A'!A4
    ='Page A'!A5
    ='Page A'!A6

    Each value on Page A is separated by an empty row. I will move the contents on Page A down 2 rows; entering another new value in A1 followed by another empty row. For example, on Page A, I now insert 2 new rows, therefore the original A1 cell that contains "One" has now moved down to A3. In the new A1 cell that I just created I will enter "Zero".

    What I want is that Page B's referenced cells stay with the cell position that I have chosen to reference, not the value of the original cell that was referenced. Before the change, cell A1 of Page B is referenced to cell A1 of Page A "One". After entering the 2 new rows on Page A, I want cell A1 of Page B to stay referenced to cell A1 of Page A regardless if it is a new row or not. Currently, after entering the new 2 rows on Page A, cell A1 of Page B switches from referencing A1 of Page A and now references A3 of Page A in order to stick with the value "One" that was originally referenced. I want it to reference A1 of Page A no matter what changes are made. When working correctly, after the change, cell A1 of Page B will contain the new value "Zero"

    I have played around with using abosolute values but I can't see to get this to work. I hope I haven't confused you, I tried to be as clear as possible. Let me know if you need clarification.

    Roger

  2. #2
    Forum Expert Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: Make a referenced cell stick with the original cell coordinates when the source is mov

    Use..

    =INDIRECT("PageA!A1")
    =INDIRECT("PageA!A2") and so on

    or better still

    =INDEX(PageA!A:A,1)
    =INDEX(PageA!A:A,2)

    You could even replace the 1,2 etc with Row() if its the same row that you are referencing on Page A

    Hence,
    =INDEX(PageA!A:A,ROW())
    and copy down
    Life's a spreadsheet, Excel!
    Say thanks, Click *

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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