Results 1 to 11 of 11

20060718, 21:52 #1
 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

20060719, 05:18 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: Formula/Macro to lookup oldest product quantities (Excel 2002)
Kieran,
I think you attached the wrong workbook.

20060719, 05:43 #3
 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)

20060719, 05:44 #4
 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

20060719, 13:46 #5
 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

20060719, 17:46 #6
 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

20060719, 18:18 #7
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 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.

20060719, 18:32 #8
 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

20060719, 18:47 #9
 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

20060722, 15:10 #10
 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

20060725, 15:28 #11
 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