Hi,

I am running Office 2007 and I am trying to find a formula that will allow
me to lookup the amount of stock used and subtract it from the starting
amount.

It must be noted that the stock used does not appear in alphabetical order
and there are blank spaces in the stock used column due to the stock being in
groups according to creation made. The base Stock list is in alphabetical
order.

I have tried a VLOOKUP but it seems to only return one of the results and
does not add together all of the stock of that ID used.

Pages are named "Stock" & "Creations". All stock has been allocated an ID
number in column A and this is consistant across both sheets.

Start count is located in Stock!C:C
Stock Used is located in Creations!C:C

Stock remaining should end up in Stock!D:D

Thanks for your help. :)

On Jun 6, 3:48 pm, Kath wrote:
Hi,

I am running Office 2007 and I am trying to find a formula that will allow
me to lookup the amount of stock used and subtract it from the starting
amount.

It must be noted that the stock used does not appear in alphabetical order
and there are blank spaces in the stock used column due to the stock being in
groups according to creation made. The base Stock list is in alphabetical
order.

I have tried a VLOOKUP but it seems to only return one of the results and
does not add together all of the stock of that ID used.

Pages are named "Stock" & "Creations". All stock has been allocated an ID
number in column A and this is consistant across both sheets.

Start count is located in Stock!C:C
Stock Used is located in Creations!C:C

Stock remaining should end up in Stock!D:D

Thanks for your help. :)


Maybe...

=C2-SUMIF(Creations!A:A,A2,Creations!C:C)

This formula is to be placed in Stock!D2 (or whatever line your data
starts from)

HTH
Kostis Vezerides

Thank you very much, sorry for the late reply but for some reason I had been
unable to see any new posts after I posted this one until just now.

Thank you again. It works perfectly.

"vezerid" wrote:

On Jun 6, 3:48 pm, Kath wrote:
Hi,

I am running Office 2007 and I am trying to find a formula that will allow
me to lookup the amount of stock used and subtract it from the starting
amount.

It must be noted that the stock used does not appear in alphabetical order
and there are blank spaces in the stock used column due to the stock being in
groups according to creation made. The base Stock list is in alphabetical
order.

I have tried a VLOOKUP but it seems to only return one of the results and
does not add together all of the stock of that ID used.

Pages are named "Stock" & "Creations". All stock has been allocated an ID
number in column A and this is consistant across both sheets.

Start count is located in Stock!C:C
Stock Used is located in Creations!C:C

Stock remaining should end up in Stock!D:D

Thanks for your help. :)


Maybe...

=C2-SUMIF(Creations!A:A,A2,Creations!C:C)

This formula is to be placed in Stock!D2 (or whatever line your data
starts from)

HTH
Kostis Vezerides