Hello All,
Hope someone might be able to help, i am new to excel and for my education purposes, would like to see if the attached would work.
What i was hoping to achieve, when people pay their lottery fee's then excel can record it and keep track of what they have paid, when they paid it and when they are due to pay it again.
I have attached a workbook, any help is appreciated.
Thanks
Hello pfn100 and Welcome to Excel Forum.
Not sure that I understand all of the logic as in if 2 is deducted each Monday, why would the account balance be down to 2 only two weeks after the initial deposit?
Never the less perhaps the attached will be a starter.
Column D is populated using: =IF(D5="","",IF(D5+8-WEEKDAY(D5,2)<=TODAY(),D5+8-WEEKDAY(D5,2),""))
Note that if a deposit is made on other than a Monday that cell (see D8) will be overwritten.
Column E is populated using: =IF(D5="","",C5+E4-IF(OR(WEEKDAY(D5,1)<>2,E4=""),0,2))
Column F is populated using: =IF(C5="","",(D5+8-WEEKDAY(D5,2))+7*E5/2-14)
Note that it would seem that all deposits are due on Mondays as that is the day when debits are made against the account.
Let us know if you have any questions.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
Hello JeteMc,
I appreciate your response.
Not all payments are due on Monday, they can be paid any day during the week, for the reason that all personal are not available on Mondays, but whatever day of the week or whatever week they can come and pay, so that would be the reason for topping up the account when they can because some times i might not see some people for weeks and when i do, they will give me lottery money.
Your solution is fantastic, could column D be changed to reflect whatever day i receive payment and not just revert to the closest Monday.
Would it be possible to colour a cell in column F, if the funds for a person runs out and they are overdue, seeing a cell coloured would remind me they are overdue and need to pay.
Thanks Again.
The formula that populates F5 and down is: =IF(C5="","",D5+7*E5/2-7)
The conditional formatting for E5 and down is set so that values less than or equal to zero have a red font.
Note that I placed a 4 in cell C5 to demonstrate conditional formatting.
Let us know if you have any questions.
Thanks again for your help.
A small problem. When i type a number into cell C8,C9 i am getting nothing in Columns D or E and i get Value in Column F. i did'nt change anything so i did'nt break it.
The formula that populates column D is set up to only automatically place a date in if the date is a Monday and is less than or equal to today's date.
In the file attached to post #4, D8 is manually filled because the payment was made on a Wednesday. D9 will stay blank until Monday January 21st unless a date is typed into that cell. When a date is typed into a cell in column D, that overwrites the formula.
Column E only displays a value when there is a date in the corresponding cell in column D.
If this isn't the way that you want the spreadsheet to function, please upload another sample with a scenario (probably a couple of months so we get the idea) manually demonstrating what you have in mind. We can then try to match your manual output in columns D:F using formulas/code.
Let us know if you have any questions.
Thanks for your continued help.
My original idea, was that only column B & C could be changed manually. Columns D, E, F would always be under the control of the program
and could not be edited manually. Column B is to add new members that wish to join the lottery syndicate. Column C is to enter the amount the person is paying.
Column D is just to show what date they have paid on, just in case there is any questions or queries about amount owed, at least then i can show what date they paid
Column E is to record what a person currently has remaining in their account and if they decide to pay in extra, it then calculates their total in account.
(This is necessary, because some people give me lotto money and might not see me for weeks and then when they meet me again they will give me more in case they don't see me for
weeks again. so they can have some money left when they pay again and column E records this).
Column F corresponds with Column E more so, it will tell me how much time they have left before they have to pay again.
i was just using Monday as a day to deduct money, it can be any day. But every week €2 has to deducted from their account, no matter what day it is.
if every one paid on a Monday it would be easier, but the nature of the business means seeing some people every couple of weeks.
The idea, if i am missing any day and some body else has to enter money, then they are just using column C and they should not even be able to change any other columns otherwise they could mess the whole process.
The attached may be helpful, if not then I would suggest seeking help on conditionally time stamping for the values in column D, from the contributors on the Excel Programming / VBA / Macros forum.
Note this proposal employs iterative calculation so be sure to select Options > Formulas > Enable iterative calculations.
The formula used to populate D6:D12 is: =IF(C6<>"",IF(D6="",TODAY(),D6),IF(D5="","",IF(D5+7<=TODAY(),D5+7,"")))
Let us know if you have any questions.
Thanks JeteMc for your time and solution.
You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks