Results 1 to 7 of 7

Thread: Payment book help

  1. #1

    Smile Payment book help



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi,
    I read up on the pivot table and not to sure I need anything that complex. I have beat my head against the wall and kind of come close to what I want to happen but alas still not gotten the results I want. I'll let one of your experts to look at it. For someone that does this a lot I'll bet it would not take them long at all. I just don't get to do this enough to keep it all familiar in my head. Use it or lose it as they say. I have listed what I was trying to do on the book pages also. Only 2 things.
    1) The date and amount entered into box A1 and C1 be inserted to the next open box in range of D12-D21 and E12-E21 where their payments 1 - 10 and the date paid is kept.
    2) Have the last date entered in D12 - D21 compared to TODAY date and IF more than 32 days have OVERDUE show in the status box
    I have attached the workbook here. I left some of the formulas I tried at the bottom of the sheet. No matter how I altered them I could not get the payment and date to one after another nor get it to see the last date entry in column D12-D21.
    Thanks for your help,
    Peace.
    Mark
    Attached Files Attached Files

  2. #2
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,820
    Articles
    0
    Excel Version
    O365
    What not just use

    =IF(MAX(D1221)<=TODAY()-32,"Overdue","Current")


    in D27

  3. #3
    HI Bob, Thanks for the reply. Looks like that will work. Ideas on the first question. 1) The date and amount entered into box A1 and C1 be inserted to the next open cell in range of D12-D21 and E12-E21 where their payments 1 - 10 and the date paid is kept. All I could get to happen was nothing or top cell entry. Would not go to second , third, and so forth. Thanks :smile

  4. #4
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,401
    Articles
    45
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Hey Mark,

    The challenge I see is that the formulas will recalculate whenever you add new data. So when you overwrite with a new date, that becomes the focal point of the logic.

    Now if you added another table on the side that kept a list of payment dates and amounts, we could built a formula to slot those in appropriately. Does that sound like something that would work?
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  5. #5
    Hi Ken, Thanks so much for the reply. Yes we could do that. Or what you think would do the best job. What I was trying to achieve is creating an entry box that would post the payment and date to the next open cell in that range. Then Bob formula looks at that last date that was posted in that range and sees if it was 32 days old compared to today's date and display as current or overdue payment. It not so much that he was looking for something that was a month past due. Just that having a couple hundred or so some of these accounts some would get months and months past due with out him catching them going so long and thought their would be a way to track the accounts without having to open each sheet to see if they are at least some what current. From the payment book pages I am using a report page in the book that will display the company name and whether it is past due or current. He would then be able to scroll the report page and see the results. Thanks for the help, Mark

  6. #6
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,401
    Articles
    45
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Mark,

    The more I think about this, the more I wonder if we're overthinking this. You already have a table to hold this info... it's in D12:E21 on each sheet. Wouldn't it make most sense to just have the user enter that data into those cells directly? It's going to save you a lot of time and effort in trying to build something to deal with it.

    My temptation would be to shade the data entry cells in another colour (I typically use light green and tell my users that "green means go". That way they know where to put it, and it saves me adding complicated backflips to the file that need to be maintained...
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  7. #7
    HI Ken, Thank you for the reply. I thought that too. I was just trying to create an easy entry box that would be uniform to all pages but the direct entry would do if the other way is to complicated and over kill. Thanks for the ideas and help. Good luck in your new venture.
    Peace.
    Mark

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •