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

ZMinior

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.

Michelle Choate 2

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

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!