Inventory check

rruchty

Help please! I have an inventory issue I need to work on. I have a production schedule where my production team adds line items for the PO they are supposed to work on. Each Line item may have the same item number, and could have the same quantity. I am using data shuttle to pull in my available inventory. From there I am doing a vlookup for inventory availabe based on the item number. When a line item is added I want it to lookp item, check the available inventory mark the status from pending or inventory issue to ready for production if there is enough inventory. (right now it is a manual check and I want to try and automate this.) Then subtract the quantity available on inventory available and move to the next line item and etc. Do I need a new sheet that copies the line items, checks the inventory and then updates my production schedule? I am not sure where to go from here.

Production Schedule

Inventory available

Inventory check - not sure if this is necessary

Georgie

Hi @rruchty,

You could definitely automate the inventory check and the status with formulas. However, subtracting the quantity available on the inventory sheet is more complex and I haven’t found a way to do that going forward, since it would require changing the formulas to use a new column with the updated inventory number, which isn’t feasible as this would require more manual work. 

However, I’ll share what I managed to come up with in case it’s useful to you!

On the Production Schedule sheet, in the Inventory Check column, I used the following formula:

• =IF(INDEX({Available}, MATCH(Item@row, {Item}, 0)) <= Quantity@row, "Not enough", IF(INDEX({Available}, MATCH(Item@row, {Item}, 0)) > (Quantity@row + 3000), "Plenty", IF(INDEX({Available}, MATCH(Item@row, {Item}, 0)) > (Quantity@row + 2000), "2000 left after order", IF(INDEX({Available}, MATCH(Item@row, {Item}, 0)) > (Quantity@row + 1000), "1000 left after order"))))
• {Available} is the Inventory Available column on the Inventory Available sheet (see Create cross-sheet references).
• {Item} is the Item column on the Inventory Available sheet.
• You can change the criteria and values returned as desired.

In the Status column (on the Production Schedule sheet), I used this formula:

• =IF([Inventory Check]@row = "Not enough", "INVENTORY ISSUE", IF([Inventory Check]@row = "Plenty", "READY FOR PRODUCTION", "PENDING"))
• Again, you can change the criteria and values returned as desired.

I then created a new column on the Inventory Available sheet with this formula:

• =IF(INDEX({Status}, MATCH(Item@row, {Item}, 0)) = "READY FOR PRODUCTION", [Inventory available]@row - SUMIFS({Quantity needed}, {Item}, Item@row, {Status}, "READY FOR PRODUCTION"))

This formula sums the quantities for the item where status is "READY FOR PRODUCTION", and subtracts this value from the inventory. As I touched on earlier, this works initially, but the next time the item is added in the production schedule, the formulas would use the initial inventory value and so this doesn’t work going forward.

I found this Inventory Management template which may be of use to you. If you still haven’t found what you’re looking for, I’d suggest reaching out to our Sales team to discuss the possibility of building a rolling inventory management solution.

Hope that helps!

Georgie

Georgie

Hi @rruchty,

You could definitely automate the inventory check and the status with formulas. However, subtracting the quantity available on the inventory sheet is more complex and I haven’t found a way to do that going forward, since it would require changing the formulas to use a new column with the updated inventory number, which isn’t feasible as this would require more manual work. 

However, I’ll share what I managed to come up with in case it’s useful to you!

On the Production Schedule sheet, in the Inventory Check column, I used the following formula:

• =IF(INDEX({Available}, MATCH(Item@row, {Item}, 0)) <= Quantity@row, "Not enough", IF(INDEX({Available}, MATCH(Item@row, {Item}, 0)) > (Quantity@row + 3000), "Plenty", IF(INDEX({Available}, MATCH(Item@row, {Item}, 0)) > (Quantity@row + 2000), "2000 left after order", IF(INDEX({Available}, MATCH(Item@row, {Item}, 0)) > (Quantity@row + 1000), "1000 left after order"))))
• {Available} is the Inventory Available column on the Inventory Available sheet (see Create cross-sheet references).
• {Item} is the Item column on the Inventory Available sheet.
• You can change the criteria and values returned as desired.

In the Status column (on the Production Schedule sheet), I used this formula:

• =IF([Inventory Check]@row = "Not enough", "INVENTORY ISSUE", IF([Inventory Check]@row = "Plenty", "READY FOR PRODUCTION", "PENDING"))
• Again, you can change the criteria and values returned as desired.

I then created a new column on the Inventory Available sheet with this formula:

• =IF(INDEX({Status}, MATCH(Item@row, {Item}, 0)) = "READY FOR PRODUCTION", [Inventory available]@row - SUMIFS({Quantity needed}, {Item}, Item@row, {Status}, "READY FOR PRODUCTION"))

This formula sums the quantities for the item where status is "READY FOR PRODUCTION", and subtracts this value from the inventory. As I touched on earlier, this works initially, but the next time the item is added in the production schedule, the formulas would use the initial inventory value and so this doesn’t work going forward.

I found this Inventory Management template which may be of use to you. If you still haven’t found what you’re looking for, I’d suggest reaching out to our Sales team to discuss the possibility of building a rolling inventory management solution.

Hope that helps!

Georgie