Calc How to reference a Cell that moves.?

English
#1

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…

#2

Yep, 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???

#3

yep, 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.

#7

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

  • 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

#8

Well 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. :slight_smile:

#9

If 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.

#10

the 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.

#4

I 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.

#5

Thanks for that. Not sure I understand. I’ll work on it. Here’s an example of what I’m trying to do
link text

#6

Use 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.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.