Figuring out inventory levels
 

Help!

I have a 3 page spreadsheet that I need to maintain. The first page is a
table that basically has part number, attribute A, attribute B and starting
inventory level. Attributes A and B are unrelated to this exercise and can be
ignored.

Page 2 has all of the customer orders, the only 2 cells that matter are are
part number and quantity.

On page 3 I would like to have a list with each of the part numbers and the
current inventory level.

Theoretically, I need a formula like a vlookup on page 3 that will look at a
value (the part number), find the initial starting inventory from page one,
and then subtract all of the quantities for that part on page 2. The net
number should be inventory on hand.

This sounds simple but I am racking my brain trying to figure it out.


The second page

Figuring out inventory levels
 

hi
example(I simplflied for test)
part number on sheet1 A2
Start Qty on sheet1 B2
--------------------------
customer on sheet 2 A2
Part ordered on sheet2 B2
Qty ordered on sheet2 C2
------------------------------
part number on sheet3 A2(same as sheet1 & same cell 2)
Current QOH on sheet3 B2 =
=Sheet1!B2-SUMIF(Sheet2!B2:B6,Sheet1!A2,Sheet2!C2:C6)

adjust to suit.

regards
FSt1


"JSF" wrote:

Help!

I have a 3 page spreadsheet that I need to maintain. The first page is a
table that basically has part number, attribute A, attribute B and starting
inventory level. Attributes A and B are unrelated to this exercise and can be
ignored.

Page 2 has all of the customer orders, the only 2 cells that matter are are
part number and quantity.

On page 3 I would like to have a list with each of the part numbers and the
current inventory level.

Theoretically, I need a formula like a vlookup on page 3 that will look at a
value (the part number), find the initial starting inventory from page one,
and then subtract all of the quantities for that part on page 2. The net
number should be inventory on hand.

This sounds simple but I am racking my brain trying to figure it out.


The second page

Figuring out inventory levels
 

forgot to mention.
you could probably put the QOH on sheet 1 and avoid duplication of data and
file bloat.
my thoughts
FSt1

"FSt1" wrote:

hi
example(I simplflied for test)
part number on sheet1 A2
Start Qty on sheet1 B2
--------------------------
customer on sheet 2 A2
Part ordered on sheet2 B2
Qty ordered on sheet2 C2
------------------------------
part number on sheet3 A2(same as sheet1 & same cell 2)
Current QOH on sheet3 B2 =
=Sheet1!B2-SUMIF(Sheet2!B2:B6,Sheet1!A2,Sheet2!C2:C6)

adjust to suit.

regards
FSt1


"JSF" wrote:

Help!

I have a 3 page spreadsheet that I need to maintain. The first page is a
table that basically has part number, attribute A, attribute B and starting
inventory level. Attributes A and B are unrelated to this exercise and can be
ignored.

Page 2 has all of the customer orders, the only 2 cells that matter are are
part number and quantity.

On page 3 I would like to have a list with each of the part numbers and the
current inventory level.

Theoretically, I need a formula like a vlookup on page 3 that will look at a
value (the part number), find the initial starting inventory from page one,
and then subtract all of the quantities for that part on page 2. The net
number should be inventory on hand.

This sounds simple but I am racking my brain trying to figure it out.


The second page