Forum Discussion
Formula to adjust the value of a cell, based on changes to drop down list options
Thank you for the wonderful post! I was not aware that there were hidden sheets!! Also, I did not have any data yet because I wanted to wait to use the document until I had full integration between the net worth and other sheets.
Using your advice I did a few things in the (with edits) sheet
- I made the starting account balance be D7, and so on.
- Then starting date was December 1, in D3.
- I also made changes based on my understanding of your formula provided to account for income as well. In the income sheet if I get paid for example I made it so that I can choose that it goes into the checking or savings, etc and that income will be added to that bank account.
- In the expense sheet I added a third column. This time to account for a transfer between accounts. Maybe I don't need this one too often but I like the option in case I've been saving for a vacation for years. I could then say I pulled money into checking from that vacation savings account to pay for the vacation.
- I input fake data along the way to see that the formulas work as I intend. It seems they do:
- In summary the income worksheet should increase whatever account is in column F.
- Then expenses sheet, column F should decrease the selected account as money is leaving that account. Column G should also decrease because that liability/debt is being paid off. And column H is only to be used if I am transferring between accounts.
- From my perspective this has been achieved but I would appreciate a double check from you for improved formulas or ideas. I learned a lot from your provided formula so any other ideas are welcome.
- Lastly, what is the best way to fill the formula to the right in the net worth sheet? Such that whenever the last day of February hits it takes the final amount available in the January column to add or subtract from. Then if I make an expense the February column should ONLY track expenses or income that have a February date. This way the numbers and chart below truly represent how my net worth truly fluctuates month by month. Another way of saying this, if I make an expense in February 2024, I don't want it to affect the January 2024 column and so on. If my final entry to expenses on January 31 brings my checking balance to 600, that 600 should now be the starting point for February. And only expenses that have a February date should be tracked from them on for February and onward. Hopefully this makes sense!
I did edit in the net worth worksheet, I intend to use the last of the month consistently to track the net worth. So I changed the initial date on column D to be 11/30/2023. Then 12/31/23 for column e, 01/31/24 On column F, and so on...
J_doe Yes, including fake data is entirely appropriate.
Your formula in E7 was incorrect, as you included the starting value ($D7) twice. The attached workbook corrects that. And I have hidden the Net Worth Worth Tracker (before edits worksheet, and shortened the name of the newer version. When you are satisfied that you don't need it, unhide and then delete that older version.
Your formulas in (column E of) the Debt section were technically valid, but with an unnecessary negative sign, subtraction (rather than addition), and multiplication by -1. My formulas in that section simply add (positive) payment amounts to (negative) balances.
OK, to compute values for any month after the starting date, there are at least two ways to do the calculations. Let's go with the following:
Each column E formula starts with prior-month values in column D and adjusts for changes from Expenses and Income transactions that occur after the month for the prior column (in D3), but on or before the last day of the month for the current column (in E3). So each SUMIFS now does two date comparisons.
If you have Excel 2021 or later, the LET function can make the formulas easier to follow. I have written formulas using the LET function in column F; for example, the formula in F7:
=LET( prior_bal, E7,
outgo_during_month, SUMIFS(Expenses!$D:$D, Expenses!$F:$F, $C7, Expenses!$B:$B,">" & E$3, Expenses!$B:$B,"<=" & F$3),
income_during_month, SUMIFS(Income!$D:$D, Income!$F:$F, $C7, Income!$B:$B,">" & E$3, Income!$B:$B,"<=" & F$3),
transfer_in_during_month, SUMIFS(Expenses!$D:$D, Expenses!$H:$H, $C7, Expenses!$B:$B,">" & E$3, Expenses!$B:$B,"<=" & F$3),
prior_bal - outgo_during_month + income_during_month + transfer_in_during_month
)requires less reading to understand than its neighbor in column E:
=D7
- SUMIFS(Expenses!$D:$D, Expenses!$F:$F, $C7, Expenses!$B:$B,">" & D$3, Expenses!$B:$B,"<=" & E$3)
+ SUMIFS(Income!$D:$D, Income!$F:$F, $C7, Income!$B:$B,">" & D$3, Income!$B:$B,"<=" & E$3)
+ SUMIFS(Expenses!$D:$D, Expenses!$H:$H, $C7, Expenses!$B:$B,">" & D$3, Expenses!$B:$B,"<=" & E$3)
If your version of Excel supports LET, then depending on your preference, you can copy either of those two columns (below the date headers) — without manual change — to the right (or to the left, as far as column E).
Note that for these formulas to work, I needed to use absolute references (i.e., prefixing the column letters and row numbers, if present, with $) for references to columns in other sheets, and conversely use relative references (without the $) for references to the cells containing the prior-month's balance.
BTW, I prefer the term "Deposit Accounts" to the original author's usage of the term "Bank Accounts". A bank account could be a deposit account or a loan account or even an investment account. And the deposit account might be with a credit union rather than with a bank. And note that other types of deposit account could include the proverbial Cookie Jar or Shoe Box or Stash in the Mattress. (No, I'm not recommending those, at least not for any substantial amounts.)