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
Bookmarks