Ask LibreOffice - RSS feedhttps://ask.libreoffice.org/en/questions/Questions and answers for LibreOfficeenFri, 11 Oct 2013 18:23:43 +0200Absolute addressinghttps://ask.libreoffice.org/en/question/23779/absolute-addressing/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?Wed, 09 Oct 2013 21:53:28 +0200https://ask.libreoffice.org/en/question/23779/absolute-addressing/Answer by JohnSUN for <p>I have the following scenario:</p>
<pre><code>Page1.A1-E1 contains dataset1
Page1.A2-E2 contains dataset2
Page1.A3-E3 contains dataset3
</code></pre>
<p><strong>Page2.B1</strong> references as follows:</p>
<pre><code>=sum($Page1.$A$1,$Page1.$E$1)
</code></pre>
<p>(sums up <strong>dataset1</strong>)</p>
<p>But now I want to swap datasets so that <strong>Page1.A1-E1</strong> contains <strong>dataset3</strong> and <strong>Page1.A3-E3</strong> contains <strong>dataset1</strong>. </p>
<p>I accomplished this by inserting a blank line before line 1, cut <strong>dataset3</strong> (now line 4), and pasted it into the new blank line 1. I cut the <strong>dataset1</strong> line (now line 2) and pasted it where <strong>dataset1</strong> had been on line 4. Then I deleted the empty line 2. </p>
<p>Now I have the following on Page1:</p>
<pre><code>A1-E1 contains dataset3
A2-E2 contains dataset2
A3-E3 contains dataset1
</code></pre>
<p>I thought that because of the absolute references, <strong>Page2.B1</strong> should now sum up <strong>dataset3</strong>. But it doesn't. Rather it re-references the addresses to follow <strong>dataset1</strong> to the new location on line 3. Why?</p>
https://ask.libreoffice.org/en/question/23779/absolute-addressing/?answer=23795#post-id-23795If you always want to have a sum of specific cells you can try to use function INDIRECT()
=SUM(INDIRECT("Page1.A1");INDIRECT("Page1.E1"))Thu, 10 Oct 2013 08:07:26 +0200https://ask.libreoffice.org/en/question/23779/absolute-addressing/?answer=23795#post-id-23795Answer by m.a.riosv for <p>I have the following scenario:</p>
<pre><code>Page1.A1-E1 contains dataset1
Page1.A2-E2 contains dataset2
Page1.A3-E3 contains dataset3
</code></pre>
<p><strong>Page2.B1</strong> references as follows:</p>
<pre><code>=sum($Page1.$A$1,$Page1.$E$1)
</code></pre>
<p>(sums up <strong>dataset1</strong>)</p>
<p>But now I want to swap datasets so that <strong>Page1.A1-E1</strong> contains <strong>dataset3</strong> and <strong>Page1.A3-E3</strong> contains <strong>dataset1</strong>. </p>
<p>I accomplished this by inserting a blank line before line 1, cut <strong>dataset3</strong> (now line 4), and pasted it into the new blank line 1. I cut the <strong>dataset1</strong> line (now line 2) and pasted it where <strong>dataset1</strong> had been on line 4. Then I deleted the empty line 2. </p>
<p>Now I have the following on Page1:</p>
<pre><code>A1-E1 contains dataset3
A2-E2 contains dataset2
A3-E3 contains dataset1
</code></pre>
<p>I thought that because of the absolute references, <strong>Page2.B1</strong> should now sum up <strong>dataset3</strong>. But it doesn't. Rather it re-references the addresses to follow <strong>dataset1</strong> to the new location on line 3. Why?</p>
https://ask.libreoffice.org/en/question/23779/absolute-addressing/?answer=23783#post-id-23783I 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.Wed, 09 Oct 2013 23:36:00 +0200https://ask.libreoffice.org/en/question/23779/absolute-addressing/?answer=23783#post-id-23783Answer by Arif at www.arifsaha.com for <p>I have the following scenario:</p>
<pre><code>Page1.A1-E1 contains dataset1
Page1.A2-E2 contains dataset2
Page1.A3-E3 contains dataset3
</code></pre>
<p><strong>Page2.B1</strong> references as follows:</p>
<pre><code>=sum($Page1.$A$1,$Page1.$E$1)
</code></pre>
<p>(sums up <strong>dataset1</strong>)</p>
<p>But now I want to swap datasets so that <strong>Page1.A1-E1</strong> contains <strong>dataset3</strong> and <strong>Page1.A3-E3</strong> contains <strong>dataset1</strong>. </p>
<p>I accomplished this by inserting a blank line before line 1, cut <strong>dataset3</strong> (now line 4), and pasted it into the new blank line 1. I cut the <strong>dataset1</strong> line (now line 2) and pasted it where <strong>dataset1</strong> had been on line 4. Then I deleted the empty line 2. </p>
<p>Now I have the following on Page1:</p>
<pre><code>A1-E1 contains dataset3
A2-E2 contains dataset2
A3-E3 contains dataset1
</code></pre>
<p>I thought that because of the absolute references, <strong>Page2.B1</strong> should now sum up <strong>dataset3</strong>. But it doesn't. Rather it re-references the addresses to follow <strong>dataset1</strong> to the new location on line 3. Why?</p>
https://ask.libreoffice.org/en/question/23779/absolute-addressing/?answer=23842#post-id-23842Absolute 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.
Fri, 11 Oct 2013 18:23:43 +0200https://ask.libreoffice.org/en/question/23779/absolute-addressing/?answer=23842#post-id-23842