Apple Event: May 7th at 7 am PT
I have a spreadsheet for my business with 3 rows: Credit, Debit, and Total. I want the Total column of each row to take the total from the row above, subtract the debit and add the credit and give me the new number. I currently do this with J7−H8+I8, but if I add a row in between what it becomes is J7-H9+I9 instead of changing to J8-H9+I9. Is there another formula that will make this work or a mode I need to set?
MacBook Pro, Mac OS X (10.7.3)
Hi Bret,
I am having trouble understanding rows and columns from your description.
A screen shot of part of your table including Row and Column labels will help.
Does your table look something like this?
Regards,
Ian.
Yes, that is exactly what I am talking about. Now, I have found that if you "Add Row Above" what is currently row 3, the new row 3 will not have any formulas in it and the row 4 formula will become =D2+B4-C4. Instead I want row 3 to be what it currently is and row 4 to be =D3+B4-C4...
Hi Bret,
I agree. Add Row Above or Add Row Below gives a blank row with no formula.
Try this: a table with two Header Rows. Header Row 2 is there to hold the Start Total (Opening Balance which I set to zero as an example).
Now every Body Row contains the same formula:
Insert a new row and it is blank (no formula) as you found:
Select D3 and Copy.
Double click on the D label to select all Body Cells in D.
Paste to fill the formula into all Body Cells of Column D.
Enter your Credit and Debit into the new Row 5
Please call back if that does not work for you.
Regards,
Ian.
Unfortunately that isn't much better then what I was already doing. I was hoping there was a function that I didn't know that simply referenced the row above and not a specific cell that might allow me to expand without the manual edits. It seems as though there isn't anything like that. Thanks for the advice though, that is a little smoother then what I was doing already.
if you "Add Row Above" what is currently row 3, the new row 3 will not have any formulas in it and the row 4 formula will become =D2+B4-C4.
How about this approach?
The formula in D2, copied down the column:
=SUM(B$2:B2)−SUM(C$2:C2)
Then, if you Add Row Above row 5, the formula appears automatically in the new row.
SG
To make sure the formula stays, pick the cell that you want to add a row underneath and press ALT (arrow direction down). This will adjust your formula so you only have to do the data entry in the the credit column and debit column.
Add to the last row, even if I add a row?
