Adding a new row with option-up arrow is messing up my formula

Question

Level 1

61 points

Adding a new row with option-up arrow is messing up my formula

Hi all.

I have a spreadsheet for my personal finances & it has several accounts side by side.

Each one has an IN, OUT & TOTAL column &, in addition to this, the credit cards have a CREDIT LIMIT column that allows me to have an AVAILABLE CREDIT total alongside the current balance.

Some time ago, a very kind soul on here helped me with a fabulous formula that allowed me to add new rows & have the totals update themselves, saving me the trouble of having to go along every column & drag the box down in order to update each column.

The formula looks like this:

Up until recently, it's worked like a charm. I select a cell (usually in the ITEM column that describes what each entry is for) type OPTION - UP ARROW & a new row is created. All the totals across all of the columns containing this formula would automatically update & it's saved me hours.

Suddenly it seems to have stopped working.

When I type OPTION-UP (or DOWN) a new row is added & all of the cells contain the correct cell references. Everything looks normal, except the numbers in the columns don't update any more.

What have I done wrong? :)

What other information might you need, in order to help?

MacBook Pro 15″, macOS 10.15

Posted on Jan 17, 2021 10:35 AM

Reply

Answer 1

Best reply

Badunit

Level 7

26,152 points

Jan 18, 2021 2:49 PM in response to del.frost

Offhand it appears normal. My recommendation is to go into Numbers/Preferences and turn off "use header names as labels" so that all cell references will be column and row vs using the words in the headers. It is a lot cleaner.

When using the header names as cell references, it can only do it for cells that have unique header names. There is only one February in the header column and only one 3,622.18 in the header row so that cell has a unique name. Blank header cells (the other two rows in your example) do not have any words to turn into a cell reference so it uses the standard cell reference.

When you have multiple header rows or columns, I'm not exactly sure how it figures out the name. I think it takes it from the lowest row and rightmost column that has text but I'm not 100% on that. And some text, even when unique, does not work. For instance, if I have a "3" in the header row it does not use it but it is okay with "3.0".

I recommend turning this feature off.

Reply

Answer 2

del.frost Author

Level 1

61 points

Jan 17, 2021 2:52 PM in response to Badunit

1)Here is a cell with zero in & out & a total:

2) Here is what the formula at the bottom looks like with that cell selected:

NOTE: This is a credit card so money paid in reduces the balance & money spent increases the balance, hence the + & - being the other way around from the OP, which was a normal bank account.

3) Here is the new cell created below it after alt-down is pressed. Notice that the new cell contains the correct amount, as though it's worked once, but it's not carried on down beyond the new row:

4) Here is the formula for the selected cell at the bottom of the screen:

5) Here I've selected the next cell down, from the screen shot above:

6) And here is the formula at the bottom of the screen:

The formula appears to be correct, but it's isn't doing what it's supposed to.

I've gone back to my 2020 sheet & added rows & it behaves normally, so I suspect this is a wrinkle I've created in duplicating the 2020 sheet, renaming & deleting everything I didn't want, rather than a programme error.

I may have to recreate the 2021 sheet from scratch & see if that solves the problem.

On the other hand, if you can see something I can't?

Reply

Answer 3

del.frost Author

Level 1

61 points

Jan 18, 2021 1:26 AM in response to del.frost

Since this seems like a replicable bug, and since you mention it cropping up in other places, this seems like a good place to put something else I've noticed that may (or may not) be related.

Here's the beginning of three rows on the left of my spreadsheet:

Here are the same three rows over on the credit card:

Here is the column header for the TOTAL column in question:

If I select the first of the three cells in the second picture above, this is what the formula looks like:

If I select the second of the three cells pictured above, this is what the formula looks like:

If I select the third of the three cells pictured above, this is what the formula looks like:

So for some reason, that middle row (75) seems to be taking the incoming 2020 total from the third header row & the word FEBRUARY from the first header column, instead of J75.

If I open up the formula & replace the incorrect value with J75, then J75 appears in a red slug, as though it's a warning of an illegal value, & when I type OK, it just goes back the the incorrect entry.

No other cells do this on any other row, but every total cell on row 75, across all of the accounts, are taking its own third column header value & the first header row value. It doesn't seem to affect the numbers, but it does seem to indicate that something is broken.

Reply

Answer 4

Badunit

Level 7

26,152 points

Jan 17, 2021 1:12 PM in response to del.frost

Are you saying that the cells in the new row get the correct formulas but the values in the cells do not match what the formulas should be giving you? That would be something I have never seen before. If this is the case, please post a screenshot with one of these cells selected that shows the formula in the formula editor, the value in the cell, and the cells being referenced in the formula (so we can calculate the result by hand).

If, instead, the new rows are no longer getting a formula, just a static value, my first guess would be you have a filter on. When a filter is on, newly added rows may/will get replaced with "the last calculated value" instead of the formula.

Reply

Answer 5

Badunit

Level 7

26,152 points

Jan 17, 2021 3:31 PM in response to del.frost

Sure enough. I can duplicate it here in a new document in the latest version. Adding a row below or above triggers it. A new bug they have introduced, I guess. It worries me that this same bug might affect other functions and the incorrect results might not be so easily discovered.

If I double-click the first messed up cell then hit enter, it clears it up. That isn't a whole lot of help if you have multiple columns of this formula, though.

Reply

Answer 6

Badunit

Level 7

26,152 points

Jan 17, 2021 3:48 PM in response to Badunit

For the heck of it I tried using INDIRECT instead of OFFSET.

=INDIRECT("r"&ROW(cell)−1&"c"&COLUMN(),FALSE)

It has the same problem but it isn't cleared up as easily. I have to delete the formula entirely so the cell is blank, then paste the formula back in to get it to work.

Use Numbers/Provide Numbers Feedback to report the bug.

Reply

Answer 7

del.frost Author

Level 1

61 points

Jan 17, 2021 4:17 PM in response to Badunit

Ah, well thanks for at least confirming it wasn't pilot error!

Before I learned this formula, every time I added a new row I would have to go along every relevant column & drag down the bottom of a box from further up to cause the formula to "heal" & make the column work.

Same as if I moved a row up or down. It would take those literal cell references & move them with the row, so again, I would have to drag down all those cell edges to make the column work again.

Looks like I'm back to doing that until they fix this.

Reply

Answer 8

del.frost Author

Level 1

61 points

Jan 19, 2021 1:18 AM in response to Badunit

I did that & the formula has returned to normal. Thanks.

I've selected "solved" for the second issue, although the main issue remains unsolved, having been identified as a bug.

I've passed it on to the Feedback page with a link to this discussion.

Reply

Adding a new row with option-up arrow is messing up my formula

Similar questions