My Inventory Project : Please help

Hey All I need some help with my inventory project.

what I am trying to do is Match my Inventory master sheet and my Daily Usage Log.

Basically, we order Tubing, and they come in crates of 1000LF each.

I tag each crate with a number when delivered. For Ex: V01-01-8-3.2

Then once the Field guys use the tubing out of the crate, they submit a form with the Crate ID and what was used for that day.

NOW

So I am trying to take the master inventory QTY and subtract the Daily report amount from it.

How do I create another page to summarize this data/ or how to have them tied together

r

Answers

  • JamesB
    JamesB ✭✭✭✭✭✭

    @Michael Stockbridge

    Do you need the individual lines on image 123456.png or does a rollup work similar to the following?


  • @JamesB

    A roll up could work.

    how my boss decided to name the crate is

    V(vender)01 (PO#) - 01 (crate #) - 8 (material Diameter) - 3.2 (thickness)

    so from 1 PO there can be multiple “Crates”

    meaning it could be:

    V01-01-8-3.2

    V01-02-8-3.2

    V01-03-8-3.2

    Etc.

  • JamesB
    JamesB ✭✭✭✭✭✭
    edited 08/01/23

    @Michael Stockbridge

    Using a rollup, you can use the following formula in the quantity used column on image 123456.png

    =SUMIF({Daily Report Range 1}, [Crate ID]@row, {Daily Report Range 2})

    Daily Report Range 1 = Crate ID column on the sheet referenced on image 321654.png

    [Crate ID]@row is your Crate ID column on the sheet in image 123456.png

    Daily Report Range 2 = Material Quantity Used column on the sheet referenced in 321654.png

    Then you just need to use =SUM(1000 - [Quantity Used]@row) in your Quantity Remaining column on image 123456.png.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!