Budget sheet question

[carew97]

I've developed a spreadsheet for my personal budget. Column D is amount for all bills or income for the entire month and E is for outstanding bill or income for the month. Column B is currently using data validation with the options of "Paid" or "Not Paid" to pick from the dropdown list. I would like to make a change where If column B is changed from "Not Paid" to "Paid" it will automatically change column E for each specific cell within that column to reflect that cell to display 0. I've looked at options from If statement and others but struggled to find the right function to make this happen. Any suggestions? If I need another approach to make this happen, that would be no problem. Thanks!

 

[Joe4]

Welcome to the Board!

I think it would be most helpful if you could post a small sample of your data, and show us 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.

 

[carew97]

Welcome to the Board!

I think it would be most helpful if you could post a small sample of your data, and show us 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.

Thanks Joe! I have a Mac. Do you have the tool explanation for Mac Excel? thanks again.

 

[Joe4]

It says it should work for Excel 365 for Mac's, and it looks like that is what you say you have.
Did you try it?

 

[carew97]

My file VIA XI2bb

Budget (version 1).xlsb
	B	C	D	E	F	G
10	Monthly Bills and Income
11	Paid Status	Due Date	Bill	Amount	Outstanding Payment	Payment Type
12		7/31/23	Fiber	$30.00	$30.00	American Express
13		8/2/23	Car Loan	$450.26	$450.26	Checking Account
14		8/1/23	Barclay	$50.00	$50.00	Checking Account
15		8/1/23	Mortgage	$1,500.00	$1,500.00	Checking Account
16		8/18/23	Trailer	$330.98	$330.98	Checking Account
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
B12:B16	List	=Sheet2!$A$1:$A$3

 

[carew97]

Here is a better example of what I want showing Paid and not Paid.

Budget (version 1).xlsb
	B	C	D	E	F	G
10	Monthly Bills and Income
11	Paid Status	Due Date	Bill	Amount	Outstanding Payment	Payment Type
12	Paid	7/31/23	Fiber	$30.00	$0.00	American Express
13	Paid	8/2/23	Car Loan	$450.26	$0.00	Checking Account
14	Paid	8/1/23	Barclay	$50.00	$0.00	Checking Account
15	Not Paid	8/1/23	Mortgage	$1,500.00	$1,500.00	Checking Account
16	Not Paid	8/18/23	Trailer	$330.98	$330.98	Checking Account
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
B12:B16	List	=Sheet2!$A$1:$A$3

 

[Joe4]

Couldn't you just make column F a formula, i.e.

=IF(B12="Paid",0,E12)

Note: If it is possible to have partial payments, this may get a little trickier, and you will have to tell us how you want that to work.

 

[carew97]

Couldn't you just make column F a formula, i.e.

=IF(B12="Paid",0,E12)

Note: If it is possible to have partial payments, this may get a little trickier, and you will have to tell us how you want that to work.

Thanks Joe. I previously used this function but I struggled on how to properly how to write it. Thanks for resolving my issue since this was the first time I worked with the IF statement!

 

[Joe4]

You are welcome.
Glad I was able to help!