Results 1 to 11 of 11
  1. #1
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Formula/Macro to lookup oldest product quantities (Excel 2002)

    Hi!
    I'm trying to automate our FIFO (first in, first out) process on our deferred revenue calcs, however I'm thinking a macro/VBA code would be better suited for this case unless someone has some combination of Excel formulas that might work better?? I've attached a file with a detailed description of what I'm trying to do. As usual, any help would be greatly appreciated!
    Thanks,
    Lana

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Formula/Macro to lookup oldest product quantities (Excel 2002)

    Kieran,

    I think you attached the wrong workbook.

  3. #3
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Perth, Western Australia, Australia
    Posts
    190
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula/Macro to lookup oldest product quantities (Excel 2002)

    LJM,

    I played around with a simple sort and sum lay out.

    It seem to do what you want. Have a look at the attachment. (Now I have fixed the file - thanks Hans)

  4. #4
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Perth, Western Australia, Australia
    Posts
    190
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula/Macro to lookup oldest product quantities (Excel 2002)

    Hans,

    As always you are correct.

    I think it should be fixed now.

    Thanks

  5. #5
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Formula/Macro to lookup oldest product quantities (Excel 2002)

    Thanks for your response Kieran... I do appreciate it, however it doesn't quite do what I need it to yet... my spreadsheet example is quite simplified and small, however the real database is much larger and I'd like to eliminate the manual task of someone typing in the data into the data worksheet (as I did in the original example attached to show how & where to move it to)... with your formula example someone would still have to breakdown the 15,000 into the three months and type in the 3 lines and find the appropriate FIFO month to attach to it. The pivot tables will give us the running quantity totals and values (the key I'm looking for is tracking the FIFO values, ie, what cost should really be used when the product gets sold). My lengthy notes inside the spreadsheet outline the movements needed... hopefully I've clarified it a bit more in this note, sorry about that, and thanks again for giving some input!
    Thanks,
    Lana

  6. #6
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Formula/Macro to lookup oldest product quantities (Excel 2002)

    Hello again... anyone out there got any other ideas/options on this one????
    Thank you!
    Lana

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Formula/Macro to lookup oldest product quantities (Excel 2002)

    Please remember that all Loungers, including WMVPs, Moderators and Admins, are volunteers spending some of their free time to help others. Although you'll usually get a reply pretty quickly here, it all depends on who's online and on how complicated the question is. This one is a rather tall order.

  8. #8
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Formula/Macro to lookup oldest product quantities (Excel 2002)

    This is true, it is a tall order... I apologize for my large request as well as my impatience! I've been spoiled by your immediate responses in the past, and because you are so knowledgeable I assumed that it was just a quick thing to throw together... I should know better than to assume anything as I hate it when people to that to me (here at work). Sorry Hans!
    Lana

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Formula/Macro to lookup oldest product quantities (Excel 2002)

    I have not had a chance to work on this, but as an approach, one could do similar to your "checking":
    Create pivot table to combine and count, then go thru the items in the pivot table to remove get the max that are used, after each step refresh the pivot table to get the next value > 0.

    The problems may arise that since you don't use real dates that the pivot table will not be sorted correctly.

    Also what happens if you want to remove more than is available? Should it do nothing? do what it can and forget the rest? Should a sheet be created with unfillable orders?

    Steve

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Formula/Macro to lookup oldest product quantities (Excel 2002)

    How about this code, it seems to work with your example set:


    Steve

  11. #11
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Formula/Macro to lookup oldest product quantities (Excel 2002)

    Hi Steve,
    This code works great! Now I need to review what you wrote so I can see exactly what it's doing... I've learned a lot from all the different code that you and others from Woody's have created for me, so I'm getting better at applying bits & pieces of the various code to different projects that come about on a daily basis. I really do appreciate you taking the time to write this code, as well as helping me learn the wonderful magic that VBA has to offer!! I really love this stuff!
    Thank You!!
    Lana

Posting Permissions

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