

LinkBack  Thread Tools  Display Modes 
#1




Running balance column formula not auto adjusting properly in row below when a row is inserted.
Hello,
When I insert rows into my table, the running balance formula doesn't properly adjust. Any help would be greatly appreciated. I have a table for financial projections into which I input any credits or debits I'm expecting so I often need to add and delete rows. =IF(AND(ISBLANK(C5),ISBLANK(D5)),"",SUM(E4,C5,D5)) This formula just sums the running balance from the row above (E column) and the credit and debit from the same row (C and D column). The IF part of the formula just keeps the running balance column blank below the last credit or debit entry (when the credit and debit entries are both blank). This is not necessary so if I need to lose this function that's fine. In the example above if I insert a row above row 5, the inserted row's formula adjusts correctly but the row below becomes =IF(AND(ISBLANK(C6),ISBLANK(D6)),"",SUM(E4,C6,D6)) The E4 cel reference should have become E5 (ie the row above). All the rows below row 6 adjust correctly. Only the row below the one inserted ever contains this error. Of course, I could just keep copying and pasting the formula back in every time but this gets tedious. I'm sure plenty of you know what is going on. If you have time to enlighten me I'd be grateful. Last edited by JPollard; 11082018 at 05:39 AM. Reason: To correct my explanation. 
#2




I'm sorry, I don't know how to delete this first reply.

#3




I had to replace this post with a better and I hope correct formula unless a better solution comes up.
Enter the following formula in: E1 = C1D1 E2 = SUM($C$1:$C2)SUM($D$1:$D2), copy the formula down the rows in Col E. 
#4




Quote:
Thanks so much for that. I found this worked well. I found I could enter the full formula in row 1 and copy it down without the need for the first shorter formula. Also, I found it worked with my added... =IF(AND(ISBLANK(C4),ISBLANK(D4)),"",SUM($C4:$C$4)SUM($D4:$D$4)) so that the balance column remains blank under the last entry. I'm fussy like that. Now I just need to figure out why it works to deeper my learning a little bit! 
#5




Glad to be of help. This forum is full of very nice and very helpful people. You could shorten the formula a bit.
=IF(AND(C4="",D4=""),"",SUM($C$4:$C4)SUM($D$4:$D4)) 
Thread Tools  
Display Modes  

Similar Threads  
Thread  Thread Starter  Forum  Replies  Last Post 
Keep balance column empty if no amount entered  baes10  Excel  3  08142018 06:31 AM 
Adjusting width of one column  cincihal  Word  5  03082018 10:01 PM 
Word 10 Tables: Column to auto number 1.1, 1.2 etc in sequential rows  FrauBaker12  Word Tables  7  06242016 11:52 PM 
Autoadjusting Formula (by date)  ShankedS  Excel  2  12032014 03:56 PM 
Adjusting column widths  norwood  Word VBA  0  09242013 06:53 AM 