• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Fifo allocation of inventory & extract name of associated bin & store name

G

Gaurav Vohra

New Member
Hi!, I'm trying to work out a formula based solution to a problem but unsuccessful. I believe somebody here can help with this problem.
I've got Two sheets:

1) Sheet1: PO data & tracker sheet for qty. allocated for each item from a particular bin no. & store location.
In this sheet, we create entries for PO no., item name, qty. required for item etc. Column A to E. All further columns in sheet 1 is auto-updated from sheet2. In the attached file I've shown this sheet to be static but in reality, PO data entries will keep on increasing with each passing day.

2) Sheet2: In this sheet, we store information for each item for its bin no., qty. of that item in that bin, & store name. For this sheet, I've shown variable data with each passing day.

What we want to achieve: In sheet 2 if we fill "yes" in release execution, column W, then all items in this bin should be allocated in sheet 1 in front of matching item along with bin no., store location.

The logic of quantity allocation is that qty. of an item is allocated as we move up to down on a FIFO basis. When Qty. need in all the above po's is completed then the only qty. overflows to next po. Also, none of the po can be allocated more qty. then mentioned in PO qty.

The attached file should make it more clear as I've shown data on daily progression basis from day 1 to day6.
 

Attachments

  • FIFO allocation of inventory along with bin no. & store name.xlsx
    21.7 KB · Views: 16
Gaurav Vohra said:
Hi!, I'm trying to work out a formula based solution to a problem but unsuccessful. I believe somebody here can help with this problem.
I've got Two sheets:

1) Sheet1: PO data & tracker sheet for qty. allocated for each item from a particular bin no. & store location.
In this sheet, we create entries for PO no., item name, qty. required for item etc. Column A to E. All further columns in sheet 1 is auto-updated from sheet2. In the attached file I've shown this sheet to be static but in reality, PO data entries will keep on increasing with each passing day.

2) Sheet2: In this sheet, we store information for each item for its bin no., qty. of that item in that bin, & store name. For this sheet, I've shown variable data with each passing day.

What we want to achieve: In sheet 2 if we fill "yes" in release execution, column W, then all items in this bin should be allocated in sheet 1 in front of matching item along with bin no., store location.

The logic of quantity allocation is that qty. of an item is allocated as we move up to down on a FIFO basis. When Qty. need in all the above po's is completed then the only qty. overflows to next po. Also, none of the po can be allocated more qty. then mentioned in PO qty.

The attached file should make it more clear as I've shown data on daily progression basis from day 1 to day6.
Click to expand...

This is for information that I've raised the same question at another forum. Link mentioned below.
https://www.excelforum.com/excel-fo...ssociated-bin-and-store-name.html#post5122894
 
Gaurav Vohra
Have You read carefully below link?

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...
chandoo.org
  • Cross-Posting. Generally, it is considered poor practice to cross post. That is to post the same question on several forums in the hope of getting a response quicker.
  • If you do cross-post, please put that in your post.
    ( You open this thread here ... yesterday ... and it too almost 24hrs to put it here... )
  • Also if you have cross-posted and get an answer elsewhere, have the courtesy of posting the answer here so other readers can learn from the answer also, as well as stopping people wasting their time on your answered question.
    .. this would mean double work for You and especially for others...
 
Hello Vletm,
First of all, I regret delayed information. I should have mentioned it at the same time when I posted the query here.
I'll take care next time.
In case I get an answer, I'll make sure to post it on both sites.
Regards.
 
You must log in or register to reply here.
Back
Top