Forum Discussion
Lookup with 3 Criterias and return next available Product Code from another sheet
I have two sheets Sales <tblSales> Inventory<tblInventory> In sheet inventory I have14 columns data. But I am interested in column Code. It is auto generated. Like WT-B-1 for same...
Or
First-in, first-out (FIFO)？
First-in, first-out (FIFO)？
I replied before,
Currently I have this part,
INDEX(Inventory!$B$2:$B$13,MATCH(1,(Inventory!$C$2:$C$13=B2)*(Inventory!$N$2:$N$13=C2),0),1)
But third part is quite logical which I didn't know how to apply,
Logic is simple,
Sum total Product with names in sales, from 1st to last<current> row. Like $A$1:$A1. Then check how much Units purchased. Subtract Sales total from purchased Units. If it is less the code is for running stock, if unit sold = purchased Units, then go-to next code for same product and name with different codes. Hope it makes sense to you.
Currently I have this part,
INDEX(Inventory!$B$2:$B$13,MATCH(1,(Inventory!$C$2:$C$13=B2)*(Inventory!$N$2:$N$13=C2),0),1)
But third part is quite logical which I didn't know how to apply,
Logic is simple,
Sum total Product with names in sales, from 1st to last<current> row. Like $A$1:$A1. Then check how much Units purchased. Subtract Sales total from purchased Units. If it is less the code is for running stock, if unit sold = purchased Units, then go-to next code for same product and name with different codes. Hope it makes sense to you.