Formula to adjust the value of a cell, based on changes to drop down list options

[J_doe]

Hi all!

I am working on an excel budget workbook. I have no idea if a formula like this is possible! It sounds so complex to me...

I have an "Expenses" sheet where in one column "D" I enter an expense. I then have column "F" which is a drop down list that lets me select the bank account that is being used to pay this expense. Then column "G" where I select, if needed, a debt account that I want to reduce.

For example, I enter in column D, $200. I want to pay those $200 from my "Checking" account that I selected in column F. This will also reduce my "Student Loan" Debt by $200.

Then I have a "Net Worth Tracker" sheet where these drop down accounts are found.

I have the following bank accounts in column C7-C11 :

Checking

Savings

Money for Shoes

Fun money

Checking 3

And The following loan accounts in C34-38:

Student Loan

Car Loan

Home Loan

Shoe Loan

Loan 4

In column D of the net worth sheet Is where I have the cell that I want to change, based on the amount I enter in column D of my "Expenses" Sheet. So for example, Cell D7 of net worth tracker sheet corresponds to the amount in my checking account. I want it to reduce by 200 because of my entry. Cell D34 corresponds to the student loan number that I want changed, and I would like it to reduce by 200.

Other things to keep in mind:

• If I ever select the "Checking" option, that amount should reduce, if I select, "Money for Shoes" that amount should reduce. If "Car Loan" is selected, that amount should change... and so on for my various accounts.

  I would like an option to set a starting balance for each of these accounts. So for example, I want to set my checking account to start at $600, and the amount will reduce based on my input in the Expenses sheet. And student loan to be -$6000 (negative because I owe it), and because I am paying it off, the negative number should be trending toward positive.

• I would like each following row in the expenses sheet that the accounts take into consideration to always be cumulative. For example, in D8 I entered $200 as my expense, paid through my checking account. Then D9 I expensed $100. D10 was $50 and so on. So in total the checking account should reduce by 350. And so on... The rows in the expense page can go for an unlimited time.

• I would like to be able to easily add or remove accounts from the formula. Say for example I get a second shoe loan. How would I add that into the formula.

• The bank account and debt account do not need to correspond to each other. For example I could set the bank account "savings", but no loan is needed. Say I had a family emergency and needed to use my savings account money. There should be no need to attach a debt/loan account. Or in the reverse. Say I was gifted a one time use gift card. I won't have a bank account for that so I don't need to select a drop down banking account option. But I used it to pay off my loan.

Let me know if you need more information or if I should rephrase something that made no sense. This is far too complex for me and I am not sure where to start looking for the proper formula! Thank you

[#2]

@J_doe 

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.

[#3]

• @J_doe 

• working on an excel budget workbook. I have no idea if a formula like this is possible

  I guess you need an online form to append new records.

  demo

 Then you can download the datas to Excel for further analysis. You can use formular,PQ or sql.

By the way,it is easy to summary or analysis if you save both deposit/balance and expenses in one sheet rather than split to several sheets.

[#4]

@SnowMan55 

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.

[#5]

@J_doe 

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.

[#6]

@SnowMan55 

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...

[#7]

@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.)