Ask Your Question
0

Absolute addressing [closed]

asked 2013-10-09 21:53:28 +0100

delijantzen gravatar image

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?

edit retag flag offensive reopen merge delete

Closed for the following reason question is not relevant or outdated by Alex Kemp
close date 2015-11-11 05:59:57.420375

3 Answers

Sort by » oldest newest most voted
0

answered 2013-10-10 08:07:26 +0100

JohnSUN gravatar image

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"))
edit flag offensive delete link more
0

answered 2013-10-09 23:36:00 +0100

m.a.riosv gravatar image

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.

edit flag offensive delete link more
0

answered 2013-10-11 18:23:43 +0100

Arif at www.arifsaha.com gravatar image

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.

edit flag offensive delete link more

Question Tools

1 follower

Stats

Asked: 2013-10-09 21:53:28 +0100

Seen: 99 times

Last updated: Oct 11 '13