Absolute addressing

[delijantzen]

I have the following scenario:

Page1.A1-E1 contains dataset1
Page1.A2-E2 contains dataset2
Page1.A3-E3 contains dataset3

Page2.B1 references as follows:

=sum($Page1.$A$1,$Page1.$E$1)

(sums up dataset1)

But now I want to swap datasets so that Page1.A1-E1 contains dataset3 and Page1.A3-E3 contains dataset1.

I accomplished this by inserting a blank line before line 1, cut dataset3 (now line 4), and pasted it into the new blank line 1. I cut the dataset1 line (now line 2) and pasted it where dataset1 had been on line 4. Then I deleted the empty line 2.

Now I have the following on Page1:

A1-E1 contains dataset3
A2-E2 contains dataset2
A3-E3 contains dataset1

I thought that because of the absolute references, Page2.B1 should now sum up dataset3. But it doesn’t. Rather it re-references the addresses to follow dataset1 to the new location on line 3. Why?

[mariosv]

I am not sure follow what you want achieve. I think last step is wrong, don’t delete empty line 2, cut from line 1 and paste in line 2 and delete line 1.

[JohnSUN]

If you always want to have a sum of specific cells you can try to use function INDIRECT()

=SUM(INDIRECT("Page1.A1");INDIRECT("Page1.E1"))

[arifsaha]

Absolute addressing does not mean it will not change with you move the destination around, rather that it will not change when you copy the reference (the cell that have that absolute reference) to other location.

If you really want a reference that never move, you should use indirect references.