Hi
Hoping someone can help with below
I have a two sheet database
Sheet one shows in columns
Product Code, Product Description, Location, Stock Count
Sheet two shows in columns
Client, Qty, Product Code, Description and Location (location field currently empty)
I am planning to use the VLOOKUP function to populate the Location field in sheet two by looking up the common Product code on both sheets.
However where I have two or more locations for a Code how can I formulate that the total qty of product located when greater than the stock in a single location moves to the 2nd (or 3rd etc location)
For example
Sheet One Shows
APP1, APPLE WHITE, A1,5
APP1, APPLE WHITE, A2,5
APP1, APPLE WHITE, A3,5
Sheet Two Shows
JOHN, 1, APP1, APPLE WHITE, LOCATION BLANK
BOB, 1, APP1, APPLE WHITE, LOCATION BLANK
MARK, 6, APP1, APPLE WHITE, LOCATION BLANK
My current formula gives me the location for all rows (clients) as A1 despite only 3 being available for Mark and what I want to formulate is the result of
JOHN, 1, APP1, APPLE WHITE, A1
BOB,1, APP1, APPLE WHITE, A1
MARK, 6, APP1, APPLE WHITE, A1 AND A2 (and if i can show 3 and 3 for each location even better)
Thanks so much!
Hayley
Bookmarks