Automatically Updated Excel Formula based on last cell updated

Hello, I’m helping my dad manage his finances, and I’m trying to use Excel to help him do that. Here’s what I need:

The image below shows a sample of what I’m trying to accomplish. Column B is the loan amount he took out, Columns D & onwards are the amount left after his payment every month. Column C is the one I’m concerned about now. For ease of calculation, let’s just concentrate on Row number 2 for now, or Loan 1, for the amount of $22,000.

https://imgur.com/a/iPH5rIn 

As you can see, the Cell C2 consistes of a simple formula (=B2-F2). I’m trying to get this formula to automatically change as my dad pays a certain amount every month. For example, when 11/21 comes along, the balance would have been updated in Cell G2, after my dad has made that month’s payment. But the formula in Cell C2 will still show the balance for the PREVIOUS month, because that formula still shows (=B2-F2). How can I make it so that this formula updates whenever a new value is added to that row? When G2 is filled in, I need the formula to update to (‘B2-G2). In Dec, when the cell H2 is filled in, I need the formula to automatically update to (=B2-G2).

I understand that this might not be a simple change. It might need a more convoluted way to calculate it. For example: Check the very last cell in that row, say Z2. If that’s empty, check the one before it, Y2. If THAT’s empty, check the one before it, X2, and so on until an non-zero value is hit. Once the value is seen to be Non-Zero, then apply the formula as (=B2-[Non-Zero Value just encountered]).

Logically, I see how it can work, but I lack the Excel expertise to make it work, or even if it CAN work in Excel. Please help me in any way you can. 

Please feel free to reach out to me in any way you need to, so I can provide m ore info if you require it. 

Thank you.

By: Vivek Wilson

Leave a Reply

Your email address will not be published. Required fields are marked *