Hi All, I've tried searching the forum for some help on creating this spreadsheet but to limited avail.
fifoattempt_updated.xlsx
Basically, what I am trying to achieve using this workbook is that, every time we sell apples (column D) we are depleting the apples that were purchased first (column C). This way, column E tells us how many apples are remaining for each of the apple purchases (ie. how many remaining of the original 300 then the additional 200).
The main stipulation that makes this a little bit difficult is that, each 'purchase' only lasts for 30 days.
- I have column B showing each purchases' expiration date.
- in my attached example, on 3/10/2014 (cell J1), we would expect for 50 remaining (and now expired) from the original purchase because all "sold apples" that occurred between 1/1 and the expiration date of 1/31 were only 250 apples.
- Then the second purchase has a remaining 100 apples due to apples sold on 2/3.
- lastly, the final purchase on 2/3 still has all of their apples since the second purchase has not been depleted or expired.
Sorry if my explanation is a little fuzzy, please see the attached workbook for what I'm trying to get at. Let me know if you have any additional questions. I prefer not to use VBA for this and make it completely formula driven; if you think this might be too difficult, let me know.
I tried to input a formula into column E but it wasn't working out too well, haha.
Thank you in advance!
Bookmarks