My Inventory Project : Please help

Michael Stockbridge

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

123456.png

321654.png

r

JamesB

@Michael Stockbridge

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

Michael Stockbridge

@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

@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.