Budget/debt sheet help!!!!

[Heatherleveridge]

Ok so I have my monthly bills on one sheet and my overall debt on the second sheet and I want some way to tell excel when I pay a monthly bill and it automatically take that amount off the total debt on the second page. I cannot figure out a way to tell excel when I make a payment.
So in my photos I have it written next to Navient how much I owe each month on sheet one, but I want some way to tell excel when I pay them. And once I mark that it’s paid have a formula on the second sheet under total amount left that will automatically subtract it.
Please help!

 

[Joe4]

Welcome to the Board!

You should be able to use a SUMIF formula in order to get the total of the payments for any particular entity on the "monthly bills" sheet.
(See: MS Excel: How to use the SUMIF Function (WS)).

So, if you have your starting balance stored in some cell somewhere, it would just be a formula structured like:
=starting balance - SUMIF(...)

If you need help setting it up, please provide us with exact details, such as what columns the entity names and amounts are in, and then what cells those are in on the "debt" sheet, as well as the cell address where the starting balance is stored.

 

[R2ah1ze1l]

Seems to me you may need a column or cell group that you can 'trigger' to cause your calculations.
Maybe make a column for 'Date Paid' while the cell is "blank" no calculations occur, when a date is entered 1/1/21 the calculation occurs.
It is kind of clunky but if you are only using a personal worksheet it will get you through.

=if(cell="","",subtract paid sum from total avail)
      ^location of date

Alternatively, just enter the value paid in an empty column and subtract the listed value from your available balance.

HTH

 

[Heatherleveridge]

Welcome to the Board!

You should be able to use a SUMIF formula in order to get the total of the payments for any particular entity on the "monthly bills" sheet.
(See: MS Excel: How to use the SUMIF Function (WS)).

So, if you have your starting balance stored in some cell somewhere, it would just be a formula structured like:
=starting balance - SUMIF(...)

If you need help setting it up, please provide us with exact details, such as what columns the entity names and amounts are in, and then what cells those are in on the "debt" sheet, as well as the cell address where the starting balance is stored.

Starting balance is in cell 'Overall Debt'!D12 and I am putting a date paid column in and the date paid will be in cell 'Monthly Bills'!D11

I am having trouble writing this formula I keep getting an error message. So if I am reading this right the excel formula will be something like =IF('Monthly Bills'!D11, ",", subtract ='Monthly Bills'!C11 from 'Overall Debt'!D12)

* ='Monthly Bills'!C11 is where I have the amount of the bill I am paying.
Also thank you so much for your help.

 

[Heatherleveridge]

Seems to me you may need a column or cell group that you can 'trigger' to cause your calculations.
Maybe make a column for 'Date Paid' while the cell is "blank" no calculations occur, when a date is entered 1/1/21 the calculation occurs.
It is kind of clunky but if you are only using a personal worksheet it will get you through.

=if(cell="","",subtract paid sum from total avail)
      ^location of date

Alternatively, just enter the value paid in an empty column and subtract the listed value from your available balance.

HTH

cell address where the starting balance is stored.

Starting balance is in cell 'Overall Debt'!D12 and I am putting a date paid column in and the date paid will be in cell 'Monthly Bills'!D11

I am having trouble writing this formula I keep getting an error message. So if I am reading this right the excel formula will be something like =IF('Monthly Bills'!D11, ",", subtract ='Monthly Bills'!C11 from 'Overall Debt'!D12)

* ='Monthly Bills'!C11 is where I have the amount of the bill I am paying.
Also thank you so much for your help.

 

[Joe4]

I am afraid your explanation does not seem to quite make sense, when coupled with your pictures. Can you post an actual example, where we can see the column and row numbers, and let us know in that example you have posted what your expected result is?

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Heatherleveridge]

I am afraid your explanation does not seem to quite make sense, when coupled with your pictures. Can you post an actual example, where we can see the column and row numbers, and let us know in that example you have posted what your expected result is?

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

I apologize for being unclear, I am just starting to dive into the Excel world as of recently. I could not figure out how to download the XL2BB on my computer (it could be because this is my company issued computer)

	December​	Date Paid​	January​	Date paid​	February​	March​	April​	May​	June​	July​
Naviant	$157.45​		$157.45​		$157.45​	$157.45​	$157.45​	$157.45​	$157.45​	$157.45​
Discover Card	$26.00​		$33.00​
Capital One 2	$100.00​		$190.00​
Auto Loan	$150.00​		$269.10​		$239.10​
Original Creditor	Total Amount Left	Interest rate	Payoff Date (if only paying minimums)	Starting amount
Naviant	$7,789.49​	9.00%​	2/26/2026​	$7,789.49​
Capital One 2	$785.56​	24.74%​	1/28/2024​	$785.56​
Discover	$304.00​	22.99%​	2/28/2022​	$304.00​
Auto Loan	$4,843.41​	22.36%​	10/29/2022​	$4,843.41​
*What I am looking for is a formula that will calculate B9-B2 once I apply a paid date in C2.

 

[Joe4]

The structure of the sheet seems very odd. You seem to have a "Date Paid" column after the months of December and January, but not any of the other months.
So I am not sure how the logic is supposed to work. Are you adding "Date" Paid" for the other months at a later point?
Do you have any control over the structure of these sheets? I think the current structure might make this much harder to do than it has to be.

 

[Heatherleveridge]

The structure of the sheet seems very odd. You seem to have a "Date Paid" column after the months of December and January, but not any of the other months.
So I am not sure how the logic is supposed to work. Are you adding "Date" Paid" for the other months at a later point?
Do you have any control over the structure of these sheets? I think the current structure might make this much harder to do than it has to be.

Yes I planned on adding a date paid column to the other months as well. Also I have complete control over it, what would you suggest would help improve the layout/structure?

 

[Joe4]

what would you suggest would help improve the layout/structure?

Anything where you are constantly having to add new columns is going to be a huge pain to maintain. You may have to update your formulas every time, unless you come up with some elaborate formula or VBA solution.

I think it would be better to have a structure of just 4 columns for the payment history, with these columns:
Entity
Payment for Month
Payment Date
Payment Amount

Then as new payments are being made, you don't need to add new columns, just new rows.
This sort of structure would be very conducive to using SUMIF (or SUMIFS) functions like I described in my first reply.