Ask Your Question

Calc How to reference a Cell that moves.?

asked 2021-04-08 03:25:22 +0200

abrogard gravatar image

updated 2021-04-08 03:26:10 +0200

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

edit retag flag offensive close merge delete


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

Astur gravatar imageAstur ( 2021-04-08 10:02:42 +0200 )edit

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

abrogard gravatar imageabrogard ( 2021-04-08 10:23:13 +0200 )edit

If there is only one cell in the column, just use the sum function in range which will ignore blank cells

Astur gravatar imageAstur ( 2021-04-08 11:19:03 +0200 )edit

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.

abrogard gravatar imageabrogard ( 2021-04-08 11:30:42 +0200 )edit

3 Answers

Sort by » oldest newest most voted

answered 2021-04-10 18:49:37 +0200

erAck gravatar image

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

edit flag offensive delete link more


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. :)

abrogard gravatar imageabrogard ( 2021-04-11 01:36:32 +0200 )edit

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.

erAck gravatar imageerAck ( 2021-04-12 19:10:46 +0200 )edit

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.

abrogard gravatar imageabrogard ( 2021-04-12 22:20:55 +0200 )edit

answered 2021-04-09 10:38:03 +0200

ajlittoz gravatar image

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.

edit flag offensive delete link more

answered 2021-04-09 08:39:14 +0200

Wanderer gravatar image

updated 2021-04-09 08:42:01 +0200

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


edit flag offensive delete link more


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

abrogard gravatar imageabrogard ( 2021-04-09 11:24:58 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2021-04-08 03:25:22 +0200

Seen: 60 times

Last updated: Apr 10