Looking for a means of keeping an updated "automatic" inventory

Is there a formula I could use that would:

1.) When "Complete?" is checked on the right side…

2.) Subtract the "Units Requested" with respect to "Lot" from the left side and populate the "Remaining Inventory"

The idea is to have a master inventory sheet that will show remaining inventories while having a secondary sheet that will serve as a place for all inventory requests using a form.

Answers

  • Michelle Choate 2
    Michelle Choate 2 ✭✭✭✭✭✭

    =[Initial Inventory]@row - SUMIFS({Units Requested}, {Lot}, Lot@row, {Complete},1)

    Michelle Choate

    michelle.choate@outlook.com

    Always happy to walk through any project you need help with! Book time with me here: https://calendly.com/michelle-choate

  • Jennifer Kurtz
    Jennifer Kurtz ✭✭✭✭✭✭

    Hi!

    You could use a SUMIFs formula there, and reference the necessary columns from your secondary sheet.

    It would look something like:

    =[Initial Inventory]@row - SUMIFS({Units Requested column from your secondary sheet}, {Lot column from your secondary sheet}, Lot@row, {Complete? Column from your secondary sheet}, 1)

    A slight variation: you could consider adding a column on your Master Inventory for "Completed requests" and put the SUMIFS formula in that column. Then, just subtract the "Completed requests" total from the initial inventory. Same result, but you'll see the numbers more clearly and can make sure the formula is working as intended. :)

    Hope that helps!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!