Formula picks up added column INDIRECT

[hebertam]

Hi, I have a spreadsheet that tracks payments made against a current balance and updates the remaining balance, and I was working on a way for the 'Remaining Balance' formula to pick up new columns that are added when a new payment comes in. I uploaded an image to hopefully clarify. I was working with the indirect function and have gotten it to partially work, but is there a way to flash fill verses manually correct for the "R1C1" in each formula? Is there a work around for this, or a better formula all together?

A couple notes:
-My actual spreadsheet has hundreds of rows, and it is not "my spreadsheet" so I cant make sweeping format changes
-Payments come in very sporadically, so I was hoping not to add a bunch columns for potential future payments as place holders
- I know if I correct "R2C" to become "R3C" for row 3, "R4C" for row 4, etc. the formula works (although this maybe a completely backwards approach in the first place, lol), but as you can see when I try to flash fill it wants to draw back to "R2C" messing up the remaining balance of each line.

Thanks!

 

[Trebor76]

Hi hebertam,

Welcome to MrExcel!!

This should do the trick (starting in cell F2 and copied down as required):

=B2-SUM(C2:INDIRECT("R"&ROW()&"C"&COLUMN()-1,FALSE))

Regards,

Robert

 

[Peter_SSs]

Welcome to the MrExcel board!

What about changing to OFFSET and in F2, copied down

=B2-SUM(C2:OFFSET(F2,0,-1))

 

[hebertam]

Thanks @Trebor76 and @Peter_SSs!! Both answers worked great...I think I am going to shift from the indirect function and use offset. Thanks again!

 

[Peter_SSs]

You're welcome. Glad we could help. Thanks for the follow-up.