I am using the latest 365 excell and want to know is there a way to make a payment amount appear into a blank cell on a chosen day example would be my monthly wage of $5,000 would appear every month on the 12th into cell W2 then automatically be added to balance in cell U2
Excel VBA Course - From Beginner to Expert
200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
Make a payment amount appear in a blank cell on a chosen dat
0
Discussion
thnx for your reply how do i attach a file here
grahamscown (rep: 8) Dec 5, '22 at 1:08 am
If Willie's answer doesn't solve your problem, to attach an Excel file, choose to Edit your original question and use the Add files... button below the text box.
John_Ru (rep: 6142) Dec 5, '22 at 3:04 am
Answers
0
Selected Answer
Graham
You can use Excel's DAY function (which returns the day part of a date) within an IF statement.
In the attached, revised file I've put this in cell W2:
=IFERROR(IF(DAY(A2)=12, 4494.31,""),"")
and fill (or copy) down and the value in bold above will appear on the 12th of whatever month (row 13 in your file).
REVISION:
To create the balance in column U, I put your opening balance of $600 (from 30 November) in sheet DECEMBER22, cell T1. This is the Opening Balance for entire the workbook, in other sheets,T1 is the balance from the end of the previous month (cell U33 of the previous tab).
In cell U2, I then added any incomes that day to that balance, less any bills to give the balance at the end of that day:
=T1 + SUM(W2:Y2) - SUM(B2:R2)
The formula for cells U3 and down is like:
=U2+SUM(W3:Y3)-SUM(B3:R3)
so the day's transactions affect the balance from the previous day.
(Totals line) Balance cell U33 has this formula:
=T1+SUM(V33:W33)-SUM(B33:R33)which is the opening balance for the sheet less the sum of incomes and bills.
Hope this helps. If so, please remember to mark this Answer as Selected
Discussion
thankyou will that still work with 12 monthly tabs set up
grahamscown (rep: 8) Dec 5, '22 at 10:56 pm
Graham,
Did you take a look at the file I made? It has 12 monthly tabs. The tabs are for recording expenses. There is a sheet for recording income(s) for each month. The only thing I didn't include was recording the date because while there will be regular once-per-month expences there are others which will likely be more than once (dining out, etc). I felt the date was not all that important - that level of detail is available on bank and credit card statements.
Cheers
Did you take a look at the file I made? It has 12 monthly tabs. The tabs are for recording expenses. There is a sheet for recording income(s) for each month. The only thing I didn't include was recording the date because while there will be regular once-per-month expences there are others which will likely be more than once (dining out, etc). I felt the date was not all that important - that level of detail is available on bank and credit card statements.
Cheers
WillieD24 (rep: 547) Dec 6, '22 at 12:42 am
Graham. Glad that worked for you. Please decide which Andser you prefer (Willie's or mine) and mark that one as Selected- that is our only reward for helping you. Thanks in advance.
John_Ru (rep: 6142) Dec 6, '22 at 1:46 am
Please look at the file i have uploaded with johns formulas added the only think i see in this dec tab is cell u33 is not the correct total remaining the original tab john designed is january Can this be fixed
Willie i do require the dates on my sheet so i can see what dates expexted payments are due to come out incase of any unexpected bills arise
Willie i do require the dates on my sheet so i can see what dates expexted payments are due to come out incase of any unexpected bills arise
grahamscown (rep: 8) Dec 6, '22 at 5:50 am
Graham. Please see my revised Answer - it corrects my error in the previous file (I forgot that U2 is the balance at the END of the day and you have bills on that day so U2 is no longer the opening balance).
Kindly mark one of your Answers as Selected (preferably this one!)
Kindly mark one of your Answers as Selected (preferably this one!)
John_Ru (rep: 6142) Dec 6, '22 at 7:33 am
0
Hello grahamscown and welcome to the forum.
Not sure what exactly you are wanting to do. Create an Excel version of your bank statement? Track expenses and income? Is U2 the total of your wages minus expenses?
If you could provide a sample file (with no personal info) it will help us to help you.
If you are looking for a simple way of tracking your income and expenses by month with a summary sheet, the attached file (which I made in 2013 using Office 2007) may be what your are looking for.
>> update 12/6/22
I see you have decided to go with John's version which is great - you found a solution to your problem.
I tweaked my original file to allow entering expenses by calendar date. I still have income sources and expenses entered on separate sheets. I kept this method because it is very likely there can be more than one income source and the date and amount probably won't remain constant (while some might).
This revised file is attached: Expense2.xlsm
Thanks for providing me the opportunity to create a new version. ;-)
Cheers