Forum Discussion
Formula to adjust the value of a cell, based on changes to drop down list options
This may be easier than you expected. (Of course, with any financial tracking, accurate recordkeeping is important.) In the attached workbook, I created two sets of formulas (actually, four sets, as I describe later) to do those calculations — one set for the deposit (bank) accounts, and one set for the loan accounts.
On the Net Worth Tracker worksheet, each row in one section (deposit accounts or loans) uses a similar formula — so similar that it can be copied to rows below it. Each of those formulas checks cells in column F of its row to retrieve the initial balance, and column C of its row to find the account name (which must be unique).
The primary effort in the calculations is accumulating the "Expense Amt" values, but for only the rows that have a matching deposit account name or loan name. I used Excel's built-in function SUMIFS to accomplish this. (You might find this description more understandable.) SUMIFS is supported in most versions of Excel since version 2010.
So adding a deposit account or loan is as easy as:
- copying an existing row to the bottom of those sections, and changing the names and initial values (the order does not matter, so you could actually insert the row among existing entries); and
- changing the data validation rule (specifically, expanding the range in which to find the relevant account names) for either column F or column G on the Expenses worksheet.
Now, the reason for the second two sets of formulas — in column E — of that worksheet is that not all transactions are instantaneous. When you write a check, you probably are mailing it to the loan processor. And after they receive it, it will take time for them to present the check to your bank, for the bank to transfer funds accordingly, and for the loan processor to credit the payment to your loan. So I recommend including data for the Transaction Cleared Date on the Expenses worksheet, which is then used in formulas for Balance (cleared) on the other worksheet. Of course, if you place those data in other columns, your column E formulas will be different.
Side note: The dollar signs inside these formulas are not required, but are helpful if you decide to copy existing formulas to a new column on that worksheet. The dollar sign is used in "mixed addressing" (as here) and "absolute addressing". (This is how Microsoft describes relative addressing (the default) vs. absolute addressing vs. mixed addressing: Switch between relative, absolute, and mixed references )
Data columns for Check Date / Deposit Date data and Description / Note data are optional, but you will likely find them useful.
Hi There!
I appreciate your response! I tried to apply your formulas to my sheet but I was not able to get it to work. I also tried to get the formula to work whenever I made changes to the "income" sheet. So that for example, I could get my paycheck from work, and select for that to automatically go into my checking. Or maybe that month I decide to portion a larger amount into my savings.
I am attaching the excel worksheet here and maybe you will be able to see my dilemma! I did not make this sheet, I found it through vast googling and unfortunately I am rather low level in excel skills.
The workbook that you chose takes up a surprising amount of disk space (1.28 MB) for something that is essentially empty. I see that someone has pre-allocated over 10,000 rows for an Excel table in the Expenses sheet, and similarly for the Income sheet. That seems unnecessary. Still, that does not explain the size.
Oh, I see that this workbook has many hidden worksheets. Did you know of these? You can right-click any worksheet tab and click "Unhide…" to see a dialog listing these (and unhide any that you wish). I unhid the Instructions worksheet.
Based on the web site of the original author ("a Canadian working in the finance industry"), I gather that someone put pieces of the original author's work together (at least two separate workbooks). But that integration is incomplete.
So, on to your problem…
I started by making a copy of the Net Worth Tracker sheet so that you can see any differences. (On the Expenses sheet and the Income sheet, the only changes I made were to their column widths.)
My first edit was to "unhide" the value in cell D7 by changing the font color to Automatic. The before-edits sheet has some odd color setting that hid the value (whether in normal display or with selected-range highlighting).
My second edit was to correct the formula, specifically the fourth parameter. You had that as Expenses!$C:$C. But since that parameter is part of the criterion that selects transactions (rows) after the Initial date, this needs to refer to your column with the (check) dates, which is column B. (Your posted description had not even mentioned dates.) The resulting formula is:=$C88 - SUMIFS( Expenses!$D:$D, Expenses!$F:$F, $C7, Expenses!$B:$B, ">=" & $D88 )
So now that formula in D7 is corrected, you might expect to see a value other than $500 displayed in that cell, because of the mortgage payment on the Expenses sheet, but the cell displays $500. If that is your expectation, the problem is in your data. I remind you again: Accurate recordkeeping is important. You have an "Initial Balance" for checking of $500 as of 12/15/2023, but the date of the mortgage payment (row 9 on the Expenses sheet) is before that: 12/08/2023.
Which brings us to the topic of dates. You did not describe that you intend to have a separate column of tracked values for each month (or possibly other interval). So the current-value formulas need to be different, both for different date selection and because you will presumably want to include values from the Income sheet (which you also did not mention in the original post).But I don't really want to provide a set of replacement formulas until you have decided on a consistent set of dates for row 3. (Even the original author has inconsistent values in that row.) What tracking date do you intend: the start of the month (as in cell D3) or the end of the month (as in cells E3 through AA3)? Intermediate values (such as every/every other Friday, or the 15th day of the month plus the last day of the month) are also possible.
Regardless of which tracking dates you want to define, if you are able to use the same Initial Date for all your existing accounts, you could skip the use of cells down around row 88 and instead put the single Initial Date into cell D3, use (the numbered rows of) column D to contain those initial monetary amounts, and put current-value formulas only in (the numbered rows of) columns E and later. I recommend this.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...
