Checkbox - adding rec'd inventory to 'In Stock number' when un checking box

Linda F

I need help to add back order inventory to In stock field when the check box is 'unchecked'. In other words, when we finally receive our back order supply list I would like the sheet to automatically add the Order size number to the In stock field number. The purple are in back order.

Paul Newcome

Ok. Then you are either going to need a starting stock column, or you are going to have to write the starting stock into each of the formulas. Using a starting stock column will make it much easier to quickly populate the formula down a lot of rows.

Linda F

THEN - one step further. I used this formula (=VLOOKUP([Inventory Item]1, {PPE Inventory Management Range 1}, {PPE Inventory Management Range 1}, 0) to reference "In Stock" number on form sheet so we can see how much inventory we currently have available. It gives me an # invalid Data Type.

Paul Newcome

How is the data input into the [In Stock] column?

For the VLOOKUP...

=VLOOKUP([Inventory Item]1, {PPE Inventory Management Range 1}, ##, 0)

You should have a numerical value in the third portion represented above by "##" which would be the column number for the column you are wanting to pull from and then the final portion should either be true or false.

Linda F

Currently, the [In Stock] is manually input but I am looking for a formula to pull from an archive sheet that contains a form. When the checkbox is checked then it will update the inventory sheet [In Stock]. For example, I order 1 box of gloves. From the Archive sheet the checkbox is checked and the [In stock] number is decreased by 1 box. Hope that makes sense.

Paul Newcome

For tracking inventory I recommend a "Starting Stock". From there you can use +/- SUMIFS to automate the adjustments. So if you have a starting stock of 50 and you submit a form saying you used 10, then the "Stock On Hand" would automatically adjust based on the SUMIFS looking for that particular item and a key that says "Used" or something to that affect. Then you would have another SUMIFS that pulls the total from forms for that item and a keyword such as "Stocked" to add what was put back into stock.

=[Starting Inventory]@row - SUMIFS({Form Total Column}, {Form Product Column}, Product@row, {Form Used/Stocked Column}, "Used") + SUMIFS({Form Total Column}, {Form Product Column}, Product@row, {Form Used/Stocked Column}, "Stocked")

Linda F

If I create a Starting Stock column in my tracking inventory then the form would need to be created in that same sheet correct? Just to break it down, this is what I have created so far:

Sheet # 1 - Supplies Request w/ form - this is where all requests for supplies will be received

Sheet # 2 - Supplies Archive sheet - Once supplies are completed and date order complete column is filled in then the row is auto moved to the Archive sheet.

Sheet # 3 - Supplies Inventory Management sheet - this has the running totals for In stock inventory and value of inventory ($).

As I understand, you are saying to create a Starting Stock column but wouldn't my "In Stock" column in the sheet # 3 serve as that also. If so, how do I tie the formula you gave me with sheet # 1 that has the form?

Paul Newcome

Are your sheets assuming that your inventory started at zero? Or did you create your sheets with (for example) 50 purple widgets already in stock?

Linda F

I started with inventory already in stock.

Paul Newcome

Ok. Then you are either going to need a starting stock column, or you are going to have to write the starting stock into each of the formulas. Using a starting stock column will make it much easier to quickly populate the formula down a lot of rows.