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

Options

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.

Tags:

Best Answer

Answers

  • Linda F
    Linda F ✭✭✭✭✭
    Options

    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
    Paul Newcome ✭✭✭✭✭✭
    Options

    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
    Linda F ✭✭✭✭✭
    Options

    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
    Paul Newcome ✭✭✭✭✭✭
    Options

    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
    Linda F ✭✭✭✭✭
    Options

    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
    Paul Newcome ✭✭✭✭✭✭
    Options

    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
    Linda F ✭✭✭✭✭
    Options

    I started with inventory already in stock.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓
    Options

    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.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!