Lookup across multiple columns within multiple sheets

[Garnet]

I have found a formula that is so close to what I need, however I just cannot
figure out the rest to make it work. This seems to be close:
=index('sheet2'!a:a,match(a1,'sheet2'!h:h,0))

Here is what I need to do. I need running sums to be entered into specific
cells on the inventory sheet (there are 280 items in column i, each needing a
formula for the running sum). This requires looking up the item number in
question (multiple entries in same column) across the received sheet (need to
add received amount) and the shipped sheet (need to subtract the shipped
amount).

In both the received sheet and the shipped sheet the item numbers are in
column a and the amount to be used is from the same row in column j. The
item number to look up is in the inventory sheet and its in column b. The
total needs to be entered into column i in the inventory sheet, on the same
row as the item number being referenced.

They look like this:

Inventory

A: Description B: Item # I: #
2: Standard A1000 ?
58: Select C1000 ?
90: Rustic D1000 ?

Received

A: Item Number J: #
2: C1000 50
3: A1000 150
4: D1000 75

Shipped

A: Item Number J: #
2: D1000 8
3: A1000 15
4: C1000 24
5: C1000 15
6: A1000 32

The question marks are the running totals I need to fill on the inventory
sheet. I also have another question if I can get this working. How can I
keep the inventory totals running accurately with the shipped and received
pages used monthly? In other words, the shipped and received pages have to
be printed then cleared monthly to be used fresh the next month. I don't
understand how to set this up and still keep the correct inventory totals. I
hope this isn't too confusing. I really need some help.

Garnet

 

[Thomas [PBD]]

Garnet,

For your column I on inventory, would the Sums be:
A1000 = 103
C1000 = 11
D1000 = 67

If so, you could use a SumIf function to say
=SUMIF(Received!A:A,Inventory!B2,Received!j:j)-SUMIF(Shipped!A:A,Inventory!B2,Shipped!j:j)

 

[Garnet]

Thank you Thomas, it works perfectly. Now I need to figure out how to keep
the running totals in the inventory sheet stable when the received sheet and
the shipped sheet are monthly sheets. Each month a new shipped and received
sheet will have to be used. How can I keep the inventory totals from
disappearing?

Garnet,

For your column I on inventory, would the Sums be:
A1000 = 103
C1000 = 11
D1000 = 67

If so, you could use a SumIf function to say
=SUMIF(Received!A:A,Inventory!B2,Received!j:j)-SUMIF(Shipped!A:A,Inventory!B2,Shipped!j:j)

--
--Thomas [PBD]
Working hard to make working easy.

I have found a formula that is so close to what I need, however I just cannot
figure out the rest to make it work. This seems to be close:
=index('sheet2'!a:a,match(a1,'sheet2'!h:h,0))

Here is what I need to do. I need running sums to be entered into specific
cells on the inventory sheet (there are 280 items in column i, each needing a
formula for the running sum). This requires looking up the item number in
question (multiple entries in same column) across the received sheet (need to
add received amount) and the shipped sheet (need to subtract the shipped
amount).

In both the received sheet and the shipped sheet the item numbers are in
column a and the amount to be used is from the same row in column j. The
item number to look up is in the inventory sheet and its in column b. The
total needs to be entered into column i in the inventory sheet, on the same
row as the item number being referenced.

They look like this:

Inventory

A: Description B: Item # I: #
2: Standard A1000 ?
58: Select C1000 ?
90: Rustic D1000 ?

Received

A: Item Number J: #
2: C1000 50
3: A1000 150
4: D1000 75

Shipped

A: Item Number J: #
2: D1000 8
3: A1000 15
4: C1000 24
5: C1000 15
6: A1000 32

The question marks are the running totals I need to fill on the inventory
sheet. I also have another question if I can get this working. How can I
keep the inventory totals running accurately with the shipped and received
pages used monthly? In other words, the shipped and received pages have to
be printed then cleared monthly to be used fresh the next month. I don't
understand how to set this up and still keep the correct inventory totals. I
hope this isn't too confusing. I really need some help.

Garnet