In XL2000, I have a calendar set up. I can set the month and year, and formulas create the calendar for that month and about the next two weeks of the next month. On another sheet, I can list events (example: client name, date and time of event, activity), and the info shows up in the calendar on the correct day. (I forgot who I got this from, but big mega-thank-yous!!!!)
I'd like to set up another calendar for payments made and due. I can track this just in a worksheet, but I'd like to see it visually on a calendar - it would help in plotting other things to see on what day the money is coming in. I hope I can describe what I'm thinking well enough for y'all to understand: -- Let's say a client has a lesson every week. Each lesson is $10. -- In my sheet listing all the client's info, I input that each entry in the Activities calendar is worth $10. -- Every time I make an entry for that client on the Activities sheet, an entry populates on the Payments sheet that this client owes me $10 for this time, and has an outstanding balance of $100. -- On the Payments calendar, the client's entry shows up. -- On the Payments sheet, I make an entry for Payment Pending from this client on some date for $60. -- On the Payments calendar, an entry shows up for Payment Pending from this client for $60. (All that is the easy part. Now the part that stumps me ...) -- On the Payments calendar, the first six of the client's entries beginning from the last date the client was paid up to now show up formatted italics (or maybe a color), indicating a pending payment to cover those lessons. -- On the Payments sheet, I record Payment Received for $100. -- On the Payments calendar, ten lessons from the last date the client was paid up to now show up formatted bold (or some other color) to indicate the lesson was paid for. The way I see it now, I need to get the dates the client was paid up to, and the date the next payment will pay up to, and format every entry for that client between those dates. I'd prefer to use a formula, but I can use a macro if necessary. Maybe a UDF? The possible problem is that the visible dates for the calendar are changeable, so I can't simply start at the top and say "find this client, starting from this date" - that date might not be visible. Or maybe I can do the calculations in the Payments sheet - if this lesson entry is covered by a pending payment, mark "X" in this column; if covered by a received payment, mark "P". Add that to the entry for the lesson, and use conditional formatting: if entry ends in "X", format italics; if entry ends in "P", format bold. Is there an easier way that I'm not seeing? Or a "gotcha" that I'm not thinking of? Ed -- ---------------------------------------------------------------------------------- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts
