Lookup total and subtract with VLOOKUP

TimmyTom

New Member
Joined
Apr 1, 2018
Messages
1
Hello, I hope someone can help me. this has been driving me crazy for a few weeks.

I have 2 sheets, one named inventory and another named orders. on the orders sheet I want to be able to do a vlookup to the inventory tab and display the qty in stock, but then if the same item is listed on another order on the orders sheet I need to deduct the qty that was already displayed. The orders sheet has multiple orders with different order ID's with the same product.

I'll try to break it down a little simpler using people (SKU) and colored balls (qty of Inventory):

PEOPLE (SKU)
Mike
Sally
Tom


Colored Balls (Inventory with QTY)
Red Ball 150
Blue Ball 100
Green Ball 200


So, Order ID1 has the following People(SKU) and Colored Balls (QTY)
Mike 25 Red Balls (this would leave 125 Red Balls)
Sally 20 Blue Balls (this would leave 80 Blue Balls)
Tom 30 Green Balls (this would leave 170 Green Balls)

The next Order ID is ID2 and has the following People(SKU) and Colored Balls (QTY)
Mike 30 Red Balls (this would leave 95 Red Balls)
Sally 45 Blue Balls (this would leave 35 Blue Balls)
Tom 50 Green Balls (this would leave 120 Green Balls)

So as you can see the same people order the same items just different qty on different orders. how do I get a this to translate using a formula so that it keeps a running total of the qty already ordered. I can get it to show me the QTY of colored balls but I have not been able to subtract how many balls are being ordered if the SKU is showing up on multiple orders

thanks for helping.
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
PEOPLE (SKU)
Mike
Sally
Tom
Colored Balls (Inventory with QTY)
Red Ball 150
Blue Ball 100
Green Ball 200
So, Order ID1 has the following People(SKU) and Colored Balls (QTY)
Mike 25 Red Balls (this would leave 125 Red Balls)
Sally 20 Blue Balls (this would leave 80 Blue Balls)
Tom 30 Green Balls (this would leave 170 Green Balls)
The next Order ID is ID2 and has the following People(SKU) and Colored Balls (QTY)
Mike 30 Red Balls (this would leave 95 Red Balls)
Sally 45 Blue Balls (this would leave 35 Blue Balls)
Tom 50 Green Balls (this would leave 120 Green Balls)
red150
blue100
green200
red countblue countgreen count
orderballcolorqtyprev qtynew qty000
id1red251501251
id1blue20100800
id1green302001700when id3, red is entered
id2red30125952the previous quantity is found by
id2blue4580350
id2green501701200=OFFSET($F$30,MATCH(F38-1,$F$31:$F$37,0),-2)
id3red151253
id3blueclearly new quantity is previous quantity minus the number (15)
id3greenin C38
just repeat for blue and green
the 1 in the red count column is obtained by
=IF(B32="red",MAX($F$31:F31)+1,0)

<colgroup><col span="18"></colgroup><tbody>
</tbody>
 
Upvote 0

Forum statistics

Threads
1,213,504
Messages
6,114,020
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top