Ask LibreOffice - RSS feedhttps://ask.libreoffice.org/en/questions/Questions and answers for LibreOfficeenWed, 10 May 2017 00:25:47 +0200I want to concatenate an =, a cell value with a number and a letter to make a cell reference formulahttps://ask.libreoffice.org/en/question/94641/i-want-to-concatenate-an-a-cell-value-with-a-number-and-a-letter-to-make-a-cell-reference-formula/Cell D3 has a value of 3
Cell A3 has a value of 047853002508
In cell I3 I used the following formula: =CONCATENATE("=A",D3)
The result is cell I3 has a value =A3. I want the value to be 047853002508 which would be the result of the cell reference =A3. If I type =A3, the result is 047853002508.
Why doesn't the concatenate function treat the result as a cell reference?
I assume I'm doing something wrong and I hope I explained this correctly.
Thanks.Tue, 09 May 2017 23:26:49 +0200https://ask.libreoffice.org/en/question/94641/i-want-to-concatenate-an-a-cell-value-with-a-number-and-a-letter-to-make-a-cell-reference-formula/Answer by librebel for <p>Cell D3 has a value of 3
Cell A3 has a value of 047853002508</p>
<p>In cell I3 I used the following formula: =CONCATENATE("=A",D3)</p>
<p>The result is cell I3 has a value =A3. I want the value to be 047853002508 which would be the result of the cell reference =A3. If I type =A3, the result is 047853002508.</p>
<p>Why doesn't the concatenate function treat the result as a cell reference?</p>
<p>I assume I'm doing something wrong and I hope I explained this correctly.</p>
<p>Thanks.</p>
https://ask.libreoffice.org/en/question/94641/i-want-to-concatenate-an-a-cell-value-with-a-number-and-a-letter-to-make-a-cell-reference-formula/?answer=94643#post-id-94643Hello Biker60, you could try:
=INDIRECT(CONCATENATE("A";D3) ;1)
( change the semicolons to comma's in your case )Wed, 10 May 2017 00:04:44 +0200https://ask.libreoffice.org/en/question/94641/i-want-to-concatenate-an-a-cell-value-with-a-number-and-a-letter-to-make-a-cell-reference-formula/?answer=94643#post-id-94643Comment by Biker60 for <p>Hello Biker60, you could try:</p>
<pre><code>=INDIRECT(CONCATENATE("A";D3) ;1)
</code></pre>
<p>( change the semicolons to comma's in your case )</p>
https://ask.libreoffice.org/en/question/94641/i-want-to-concatenate-an-a-cell-value-with-a-number-and-a-letter-to-make-a-cell-reference-formula/?comment=94647#post-id-94647Both versions of the INDIRECT statement worked. Thanks erAck and librebel. I have about 1000 rows of data where I'll apply this formula. It will save me a lot of time.Wed, 10 May 2017 00:25:47 +0200https://ask.libreoffice.org/en/question/94641/i-want-to-concatenate-an-a-cell-value-with-a-number-and-a-letter-to-make-a-cell-reference-formula/?comment=94647#post-id-94647Answer by erAck for <p>Cell D3 has a value of 3
Cell A3 has a value of 047853002508</p>
<p>In cell I3 I used the following formula: =CONCATENATE("=A",D3)</p>
<p>The result is cell I3 has a value =A3. I want the value to be 047853002508 which would be the result of the cell reference =A3. If I type =A3, the result is 047853002508.</p>
<p>Why doesn't the concatenate function treat the result as a cell reference?</p>
<p>I assume I'm doing something wrong and I hope I explained this correctly.</p>
<p>Thanks.</p>
https://ask.libreoffice.org/en/question/94641/i-want-to-concatenate-an-a-cell-value-with-a-number-and-a-letter-to-make-a-cell-reference-formula/?answer=94644#post-id-94644CONCATENATE concatenates strings of its arguments, if an argument is a cell reference then that is dereferenced and the result concatenated, here D3 is "3" so at the end you get the string "=A3".
You're probably looking for **INDIRECT** instead, if you want to display the value of the content of A3 in this example. So `=INDIRECT("A"&D3)` which is the same as `=INDIRECT(CONCATENATE("A",D3))`
Wed, 10 May 2017 00:04:54 +0200https://ask.libreoffice.org/en/question/94641/i-want-to-concatenate-an-a-cell-value-with-a-number-and-a-letter-to-make-a-cell-reference-formula/?answer=94644#post-id-94644