Ask LibreOffice - RSS feedhttps://ask.libreoffice.org/en/questions/Questions and answers for LibreOfficeenMon, 12 Apr 2021 22:20:55 +0200Calc How to reference a Cell that moves.?https://ask.libreoffice.org/en/question/302881/calc-how-to-reference-a-cell-that-moves/I want to add to a quantity held in a cell in column A a quantity held in a cell in column B.
The B quantity may be one row higher, or two rows higher or more... we can't know ahead of time.
For between the B cell row and the A cell row other rows get inserted according to number of inputs to the whole spreadsheet. We don't know how many that is going to be. Usually no more than three or four.
So in column B they'll be blank cells in the extra rows if they exist.
So I suppose I should have a formula the does an IF that checks for blank cells and if not blank then it takes the quantity and uses it. And if it is blank decrements the row number and looks again.
I did some googling but finished up thinking it might be quicker to come here and ask....Thu, 08 Apr 2021 03:25:22 +0200https://ask.libreoffice.org/en/question/302881/calc-how-to-reference-a-cell-that-moves/Comment by abrogard for <p>I want to add to a quantity held in a cell in column A a quantity held in a cell in column B.</p>
<p>The B quantity may be one row higher, or two rows higher or more... we can't know ahead of time.</p>
<p>For between the B cell row and the A cell row other rows get inserted according to number of inputs to the whole spreadsheet. We don't know how many that is going to be. Usually no more than three or four.</p>
<p>So in column B they'll be blank cells in the extra rows if they exist. </p>
<p>So I suppose I should have a formula the does an IF that checks for blank cells and if not blank then it takes the quantity and uses it. And if it is blank decrements the row number and looks again.</p>
<p>I did some googling but finished up thinking it might be quicker to come here and ask....</p>
https://ask.libreoffice.org/en/question/302881/calc-how-to-reference-a-cell-that-moves/?comment=302929#post-id-302929yep, that'd be a good one. but there's more than one. the way it works there's a list of quantities and that list gets broken up by having blank rows inserted under some of them, but not all. (they're not blank rows really of course, they're just blank in that column. they carry info in other columns).
so i'm looking to go into that column at a certain level and find the first quantity above that level.Thu, 08 Apr 2021 11:30:42 +0200https://ask.libreoffice.org/en/question/302881/calc-how-to-reference-a-cell-that-moves/?comment=302929#post-id-302929Comment by Astur for <p>I want to add to a quantity held in a cell in column A a quantity held in a cell in column B.</p>
<p>The B quantity may be one row higher, or two rows higher or more... we can't know ahead of time.</p>
<p>For between the B cell row and the A cell row other rows get inserted according to number of inputs to the whole spreadsheet. We don't know how many that is going to be. Usually no more than three or four.</p>
<p>So in column B they'll be blank cells in the extra rows if they exist. </p>
<p>So I suppose I should have a formula the does an IF that checks for blank cells and if not blank then it takes the quantity and uses it. And if it is blank decrements the row number and looks again.</p>
<p>I did some googling but finished up thinking it might be quicker to come here and ask....</p>
https://ask.libreoffice.org/en/question/302881/calc-how-to-reference-a-cell-that-moves/?comment=302926#post-id-302926If there is only one cell in the column, just use the sum function in range which will ignore blank cellsThu, 08 Apr 2021 11:19:03 +0200https://ask.libreoffice.org/en/question/302881/calc-how-to-reference-a-cell-that-moves/?comment=302926#post-id-302926Comment by abrogard for <p>I want to add to a quantity held in a cell in column A a quantity held in a cell in column B.</p>
<p>The B quantity may be one row higher, or two rows higher or more... we can't know ahead of time.</p>
<p>For between the B cell row and the A cell row other rows get inserted according to number of inputs to the whole spreadsheet. We don't know how many that is going to be. Usually no more than three or four.</p>
<p>So in column B they'll be blank cells in the extra rows if they exist. </p>
<p>So I suppose I should have a formula the does an IF that checks for blank cells and if not blank then it takes the quantity and uses it. And if it is blank decrements the row number and looks again.</p>
<p>I did some googling but finished up thinking it might be quicker to come here and ask....</p>
https://ask.libreoffice.org/en/question/302881/calc-how-to-reference-a-cell-that-moves/?comment=302916#post-id-302916Yep, search up the column until I find one that is not empty. I'm in no hurry. I'll see if I can figure it out while I'm waiting. Looks like I need a loop inside a cell formula. I assume that's possible? And in that loop the row will need decrementing with each iteration. Not sure how to do that. Maybe the cell location needs to be stored in a variable and I decrement the variable or something???Thu, 08 Apr 2021 10:23:13 +0200https://ask.libreoffice.org/en/question/302881/calc-how-to-reference-a-cell-that-moves/?comment=302916#post-id-302916Comment by Astur for <p>I want to add to a quantity held in a cell in column A a quantity held in a cell in column B.</p>
<p>The B quantity may be one row higher, or two rows higher or more... we can't know ahead of time.</p>
<p>For between the B cell row and the A cell row other rows get inserted according to number of inputs to the whole spreadsheet. We don't know how many that is going to be. Usually no more than three or four.</p>
<p>So in column B they'll be blank cells in the extra rows if they exist. </p>
<p>So I suppose I should have a formula the does an IF that checks for blank cells and if not blank then it takes the quantity and uses it. And if it is blank decrements the row number and looks again.</p>
<p>I did some googling but finished up thinking it might be quicker to come here and ask....</p>
https://ask.libreoffice.org/en/question/302881/calc-how-to-reference-a-cell-that-moves/?comment=302913#post-id-302913As far as I understood, you mean a macro that will search a specific range of cells for those whose contents are not empty.
I can't write such a macro, but I suggest you change the title of the question and maybe the tags so that those who can help you faster.Thu, 08 Apr 2021 10:02:42 +0200https://ask.libreoffice.org/en/question/302881/calc-how-to-reference-a-cell-that-moves/?comment=302913#post-id-302913Answer by erAck for <p>I want to add to a quantity held in a cell in column A a quantity held in a cell in column B.</p>
<p>The B quantity may be one row higher, or two rows higher or more... we can't know ahead of time.</p>
<p>For between the B cell row and the A cell row other rows get inserted according to number of inputs to the whole spreadsheet. We don't know how many that is going to be. Usually no more than three or four.</p>
<p>So in column B they'll be blank cells in the extra rows if they exist. </p>
<p>So I suppose I should have a formula the does an IF that checks for blank cells and if not blank then it takes the quantity and uses it. And if it is blank decrements the row number and looks again.</p>
<p>I did some googling but finished up thinking it might be quicker to come here and ask....</p>
https://ask.libreoffice.org/en/question/302881/calc-how-to-reference-a-cell-that-moves/?answer=303359#post-id-303359You do not need anything special. If rows are inserted then existing cell references are adjusted to still point to the moved cells. For example
- A2: `1`
- B1: `2`
- C1: `=A2+B1` => 3
Insert a row above row 2 so cell A2 moves to A3, then formula in C1 is adjusted to `=A3+B1`
Sat, 10 Apr 2021 18:49:37 +0200https://ask.libreoffice.org/en/question/302881/calc-how-to-reference-a-cell-that-moves/?answer=303359#post-id-303359Comment by abrogard for <p>You do not need anything special. If rows are inserted then existing cell references are adjusted to still point to the moved cells. For example</p>
<ul>
<li>A2: <code>1</code></li>
<li>B1: <code>2</code></li>
<li>C1: <code>=A2+B1</code> => 3</li>
</ul>
<p>Insert a row above row 2 so cell A2 moves to A3, then formula in C1 is adjusted to <code>=A3+B1</code></p>
https://ask.libreoffice.org/en/question/302881/calc-how-to-reference-a-cell-that-moves/?comment=303806#post-id-303806the danger i was thinking of was if you need the formula to be in those cells in a column in the interspersed rows. Its entirely to be expected. It's a blank row. That's what one asked for, I guess.Mon, 12 Apr 2021 22:20:55 +0200https://ask.libreoffice.org/en/question/302881/calc-how-to-reference-a-cell-that-moves/?comment=303806#post-id-303806Comment by erAck for <p>You do not need anything special. If rows are inserted then existing cell references are adjusted to still point to the moved cells. For example</p>
<ul>
<li>A2: <code>1</code></li>
<li>B1: <code>2</code></li>
<li>C1: <code>=A2+B1</code> => 3</li>
</ul>
<p>Insert a row above row 2 so cell A2 moves to A3, then formula in C1 is adjusted to <code>=A3+B1</code></p>
https://ask.libreoffice.org/en/question/302881/calc-how-to-reference-a-cell-that-moves/?comment=303767#post-id-303767If you have a cell range reference like A1:A2 in `=SUM(A1:A2)` then that is similarly adjusted if a row *in between* the two endpoints is inserted, e.g. again insert a row above row 2 (between row 1 and row 2) and the reference is adjusted to A1:A3 and the formula becomes `=SUM(A1:A3)` summing all three cells.Mon, 12 Apr 2021 19:10:46 +0200https://ask.libreoffice.org/en/question/302881/calc-how-to-reference-a-cell-that-moves/?comment=303767#post-id-303767Comment by abrogard for <p>You do not need anything special. If rows are inserted then existing cell references are adjusted to still point to the moved cells. For example</p>
<ul>
<li>A2: <code>1</code></li>
<li>B1: <code>2</code></li>
<li>C1: <code>=A2+B1</code> => 3</li>
</ul>
<p>Insert a row above row 2 so cell A2 moves to A3, then formula in C1 is adjusted to <code>=A3+B1</code></p>
https://ask.libreoffice.org/en/question/302881/calc-how-to-reference-a-cell-that-moves/?comment=303403#post-id-303403Well that's amazing isn't it? Could be a danger in some circumstances I guess.
Well thanks for that. It is the answer.
My next question is then how to find and total the amounts that appear in these interspersed rows but that should be a different question, I guess, if I can't figure it myself. I think maybe the solution to it is in 'indirect()' somewhere.
Thanks. :)Sun, 11 Apr 2021 01:36:32 +0200https://ask.libreoffice.org/en/question/302881/calc-how-to-reference-a-cell-that-moves/?comment=303403#post-id-303403Answer by Wanderer for <p>I want to add to a quantity held in a cell in column A a quantity held in a cell in column B.</p>
<p>The B quantity may be one row higher, or two rows higher or more... we can't know ahead of time.</p>
<p>For between the B cell row and the A cell row other rows get inserted according to number of inputs to the whole spreadsheet. We don't know how many that is going to be. Usually no more than three or four.</p>
<p>So in column B they'll be blank cells in the extra rows if they exist. </p>
<p>So I suppose I should have a formula the does an IF that checks for blank cells and if not blank then it takes the quantity and uses it. And if it is blank decrements the row number and looks again.</p>
<p>I did some googling but finished up thinking it might be quicker to come here and ask....</p>
https://ask.libreoffice.org/en/question/302881/calc-how-to-reference-a-cell-that-moves/?answer=303105#post-id-303105I don't understand, why your table is designed like this,
but an easy solution would be a column with a marker like`= IF(col="";"";ROW() ) `
wich will just put the current line number in the marker column. You must replace col with the real reference you want to check.
Now you can use the function MIN() to get the first occupied line in your range. MAX() can be used to find the last one before your current row.
There are more advanced possibilities like matrix-operations or the use of sumproduct, wich may solve your problem also....
J.Fri, 09 Apr 2021 08:39:14 +0200https://ask.libreoffice.org/en/question/302881/calc-how-to-reference-a-cell-that-moves/?answer=303105#post-id-303105Comment by abrogard for <p>I don't understand, why your table is designed like this,
but an easy solution would be a column with a marker like<code>= IF(col="";"";ROW() )</code>
wich will just put the current line number in the marker column. You must replace col with the real reference you want to check.</p>
<p>Now you can use the function MIN() to get the first occupied line in your range. MAX() can be used to find the last one before your current row.</p>
<p>There are more advanced possibilities like matrix-operations or the use of sumproduct, wich may solve your problem also....</p>
<p>J.</p>
https://ask.libreoffice.org/en/question/302881/calc-how-to-reference-a-cell-that-moves/?comment=303126#post-id-303126Thanks for that. Not sure I understand. I'll work on it. Here's an example of what I'm trying to do
[link text](https://imgur.com/a/DNgmKLW)Fri, 09 Apr 2021 11:24:58 +0200https://ask.libreoffice.org/en/question/302881/calc-how-to-reference-a-cell-that-moves/?comment=303126#post-id-303126Answer by ajlittoz for <p>I want to add to a quantity held in a cell in column A a quantity held in a cell in column B.</p>
<p>The B quantity may be one row higher, or two rows higher or more... we can't know ahead of time.</p>
<p>For between the B cell row and the A cell row other rows get inserted according to number of inputs to the whole spreadsheet. We don't know how many that is going to be. Usually no more than three or four.</p>
<p>So in column B they'll be blank cells in the extra rows if they exist. </p>
<p>So I suppose I should have a formula the does an IF that checks for blank cells and if not blank then it takes the quantity and uses it. And if it is blank decrements the row number and looks again.</p>
<p>I did some googling but finished up thinking it might be quicker to come here and ask....</p>
https://ask.libreoffice.org/en/question/302881/calc-how-to-reference-a-cell-that-moves/?answer=303120#post-id-303120Use another column to "normalize" your input. For instance, in column C, you copy data from column B. If the B-cell is non empty, this is the value for the C-cell; if the B-cell is empty, take the value one row higher.
Then instead of adding the B-value, you add the C-cell in the same row.
To make things clear, suppose I am on row 7. The cells in this row contain:
- A7: your data
- B7: data or void
- C7: `=IF(B7;B7;B6)`
- X7: `=A7+C7`
assuming you want the sum in column X
Column C is a scratch area. You can `Format`>`Columns`>`Hide` it once you are confident in the formula.
*To show the community your question has been answered, click the ✓ next to the correct answer, and "upvote" by clicking on the ^ arrow of any helpful answers. These are the mechanisms for communicating the quality of the Q&A on this site. Thanks!*
*In case you need clarification, **edit** your question (not an answer which is reserved for solutions) or **comment** the relevant answer.*
Fri, 09 Apr 2021 10:38:03 +0200https://ask.libreoffice.org/en/question/302881/calc-how-to-reference-a-cell-that-moves/?answer=303120#post-id-303120Answer by abrogard for <p>I want to add to a quantity held in a cell in column A a quantity held in a cell in column B.</p>
<p>The B quantity may be one row higher, or two rows higher or more... we can't know ahead of time.</p>
<p>For between the B cell row and the A cell row other rows get inserted according to number of inputs to the whole spreadsheet. We don't know how many that is going to be. Usually no more than three or four.</p>
<p>So in column B they'll be blank cells in the extra rows if they exist. </p>
<p>So I suppose I should have a formula the does an IF that checks for blank cells and if not blank then it takes the quantity and uses it. And if it is blank decrements the row number and looks again.</p>
<p>I did some googling but finished up thinking it might be quicker to come here and ask....</p>
https://ask.libreoffice.org/en/question/302881/calc-how-to-reference-a-cell-that-moves/?answer=303117#post-id-303117Thank you for that. don't fully understand it. will give it some thought, try it out. :)Fri, 09 Apr 2021 10:33:09 +0200https://ask.libreoffice.org/en/question/302881/calc-how-to-reference-a-cell-that-moves/?answer=303117#post-id-303117