VLOOKUP Stock Spreadsheet issue

[paulbray81]

Hi Guys,

I am having an issue which a spreadsheet where I have a list of stock


[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]item[/TD]
[TD]SKU[/TD]
[TD]TYPE[/TD]
[TD]SIZE[/TD]
[TD]Starting QTY[/TD]
[TD]Running Qty[/TD]
[/TR]
[TR]
[TD]Shirt[/TD]
[TD]SS-5XS[/TD]
[TD]SS[/TD]
[TD]5XS[/TD]
[TD]8[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Shirt[/TD]
[TD]SS-3XS[/TD]
[TD]SS[/TD]
[TD]3XS[/TD]
[TD]23[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Shirt[/TD]
[TD]LS-3XS[/TD]
[TD]LS[/TD]
[TD]3XS[/TD]
[TD]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Shirt[/TD]
[TD]LS-2XS[/TD]
[TD]LS[/TD]
[TD]2XS[/TD]
[TD]25[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


I then have a table which lists all items the running QTY has a formula of =F4-(VLOOKUP(C4,$J$3:$Y$10,9,FALSE))

F4 = the Starting Qty column
C4 = is the SKU column
Column J is the shirt sku column
Column 9 is the shirts needed column


[TABLE="class: outer_border, width: 1300"]
<tbody>[TR]
[TD]Team[/TD]
[TD]Shirt SKU[/TD]
[TD]Shirt Size[/TD]
[TD]Short SKU[/TD]
[TD]Short Size[/TD]
[TD]Zip Top SKU[/TD]
[TD]Zip Top Size[/TD]
[TD]Shirts Needed[/TD]
[TD]In Stock[/TD]
[TD]Shorts Needed[/TD]
[TD]In Stock[/TD]
[TD]Socks Needed[/TD]
[TD]In Stock[/TD]
[TD]Zip Top Needed[/TD]
[TD]In Stock[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]U7 Red[/TD]
[TD]SS-5XS[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]3[/TD]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]U7 Red[/TD]
[TD]SS-3XS[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2[/TD]
[TD]23[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]U7 Yellow[/TD]
[TD]SS-5XS[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]5[/TD]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

So I need to see the running quantity change upon what is added into the shirts needed. trouble is the 2nd row with the SS-5XS SKU is not added to the running total which should make it 0 not 5.

If anyone can help me solve this issue it would be massively appreciated

 

[Marcelo Branco]

Maybe...

=F4-SUMIF(J$4:J$10,C4,R$4:R$10)

M.