Keeping formulas intact when deleting rows in Numbers 3.6

Question

Level 2

176 points

Keeping formulas intact when deleting rows in Numbers 3.6

I'm running Numbers 3.6 in Yosemite. (I know it's old. I need it for music and can make it look less ugly than the new OSes.)

There was a thread on this topic here, but it didn't quite answer my question FWICT:

https://discussions.apple.com/thread/3128198

I have a simple checking account register spreadsheet that I downloaded. (I was really surprised Apple doesn't include something this basic.) Nothing I could find online followed the standard banking ledger format, so I customized it to have columns for all the usuals: date, transaction info, a debit column, a credit column, then a running balance. The debit column subtracts from the balance in the row above it, the credit column adds to it, then it calculates it and puts it in the running balance column. This is the formula in the running balance column, and is the only relevant formula cell in the whole thing:

$G47−$E48+$F48

I'm able to download my bank's spreadsheet, open it, copy and paste keeping formatting and everything is fine. (I have to manually count rows, add and select them, then paste, which seems very 1985 to me, but that another issue.)

So far, so good(ish)—until I discovered a duplicate entry. I deleted the row and instantly got the "Warning" exclamation marks down the whole balance column. Numbers seems to dynamically adjust the formulas when I'm adding rows, so why is it not behaving the same way when I delete a row? I suppose I can just change the line to "Duplicate" and the value to 0 as a workaround, but it seems like there should be a way to delete rows!

I just barely get around in spreadsheet apps. Anything beyond using basic maths in them is beyond me. I get networking, graphic design, music production and working with people. Spreadsheets are not my forte, so I'm sure the answer is extremely obvious to someone here, and I greatly appreciate the help!

Thanks!

Posted on Feb 1, 2019 8:05 PM

Reply

Answer 1

Best reply

SGIII

Level 8

35,288 points

Feb 2, 2019 5:24 AM in response to Glen Schuler

For your running balance column you might conserve using an "expanding range" formula that allows you add or delete rows without having to adjust the references. It would look something like this:

The formula in G2, filled down the column, is:

=SUM(F$1:F2)−SUM(E$1:E2)

This simply subtracts the debits "so far" from the credits "so far", which will be your running balance.

The $ anchors the beginning of the ranges to row 1 and the ranges expand depending on the position of the cell containing the formula. So any time you add a row with data all you have to do is copy the formula into that row. When you delete a row the formula continues to work as expected. No need to mess with fixing missing references.

SG

Reply

Answer 2

Barry

Level 9

59,861 points

Feb 1, 2019 11:34 PM in response to Glen Schuler

Heres a sample based on the description in your initial post.

Putting the opening balance in a header row allows you to have the same formula in every (body) cell of column F.

When filled down, the formula will track where it is and adjust to that location, always getting the cell immediately above itself (blue), subtracting the amount in the cell two columns to its left (orange), and adding the amount in the cell one column to its left (purple).

When you delete a row, you remove the 'blue' cell to which the formula in the row below refers to get the previous balance. Ideally, Numbers should immediately adjust the formula to reference the cell above the one removed. But it seems Numbers does not yet know that row is being removed, only that the cell on which a formula depends has disappeared, and throws an error message regarding a bad reference to a cell that no longer exists, (Note the lack of a 'blue' cell in the screen shot below.)

Th new F6 is, by the way, the only cell to display that error. Click the error triangle (once) and it will present the error message "The formula contains an invalid reference."

lick any the error triangle in any of the cells below the first one, and you'll see a different error message: "Cell F6 contains an error."

The Fix:

Click once on the cell above the one showing the first error flag/triangle. Hover the mouse near the bottom margin of that cell. When the Fill handle (a small, yellow circle) appears, grab it with the mouse and drag it down one cell. That fills the formula down to the cell with an error, replacing the formula in that cell with an adjusted copy from the cell above, removing the error from the target cell. With no error in that cell now, all of the error fags below should also disappear.

Regards,

Barry

Reply

Answer 3

Glen Schuler Author

Level 2

176 points

Feb 1, 2019 8:32 PM in response to Glen Schuler

And if anyone could enlighten me as to why the edit button for my own post is gone even though I'm signed in, I'd greatly appreciate it. I remember when Apple was insanely great. It seems that every year it becomes more of the first part and and less of the second part of that phrase.

Reply

Answer 4

Barry

Level 9

59,861 points

Feb 1, 2019 10:32 PM in response to Glen Schuler

"…enlighten me as to why the edit button for my own post is gone even though I'm signed in…"

Can tell you 'why' mechanically—Ability to edit times out at (about) 15 minutes after posting. You posted at nn:05, and posted your 'edit' at nn:32, 27 minutes later.

Can't tell you why Apple chose (or accepted) that time limit between post and revise, though.

Regards,

Barry

(Now looking at your question…)

B

Reply

Answer 5

Glen Schuler Author

Level 2

176 points

Feb 2, 2019 7:31 PM in response to Barry

Wow, Barry! I can't thank you enough for all of energy you put into explaining that solution. I'm so grateful and very impressed. If only Apple were the company they once were, we wouldn't be having to resolve such simple issues with workarounds. I was probably the biggest proponent of their technology since the day Public Beta came out. Was in the first OS X Tiger Server certification class ever taught off the Apple Campus and have sold millions of dollars of their product and stock. Now, I just kind of have to hang my head in shame and say, "Sorry the OS looks like it was made by color blind child slave laborers and barely gets working before they break it again, but at least it's not Windows."

You're awesome!

Reply

Answer 6

Glen Schuler Author

Level 2

176 points

Feb 2, 2019 7:55 PM in response to Barry

Oh, no! I pushed "Solved" on the wrong post, and it won't let me change it. Barry's post was the solution. I'm sorry. The least I could have done for you was give you the solution cred! My deepest apologies, but this is, again, really an issue with the way Apple are running this forum now.

SG, your suggestion was helpful, but not exactly what I want to see. The screenshots and workaround in Barry's post is exactly how an account ledger should look and behave. It should be a standard template in any spreadsheet software, and should not break by deleting a row. (To be fair, I am in the "dark ages" of Yosemite, but there has not been an OS that that my eyes can stomach until Mojave, and it requires me to update a some hardware and software.

Off topic, but my heart warms every time I screen share my G5 server (15 years and still going strong!) and see the gently pulsing dialog buttons, 3D Dock, and colors that are not literally what we call "out of the tube" (cyan, magenta, yellow, black, red, green, and blue), but beautifully blended. The icons are works of art, and colors and shapes were used to give the user the ability to find what they were seeking at a glance instead of having to read text next to monochromatic, generic icons in the sidebar and toolbar. At least with Yosemite, I can still theme it to some degree. Productivity for creative professionals is worse with every hardware and software release. They forget that we were the ones who kept them on life support before Steve Jobs and Scott Forstall came back and made the greatest products in the world with their combined genius. Ives is an insane minimalist, and we're an unhappy captive customer base. Pardon my rant. I just had to say it.

Thank you again, Barry (and SG).

Reply

Answer 7

SGIII

Level 8

35,288 points

Feb 2, 2019 11:12 PM in response to Glen Schuler

SG, your suggestion was helpful, but not exactly what I want to see. 
The screenshots and workaround in Barry's post is exactly how an account ledger should look and behave.

Well thanks for the green tick anyway. Have you actually tried the expanding ranges? If you do I think you'll find they give exactly the same look and results as with the other approach, in other words produce results that are exactly how an account ledger should look and behave too.😀 Except that with that approach you can delete rows without hassle, which I understand was one of the aims of your question.

(SG)

Reply

Keeping formulas intact when deleting rows in Numbers 3.6

Similar questions