populate data validation dropdown list using a single formula that gets multiple values from lookup

[Bali Rakhra]

I've lost a day scouring the net for a solution. So any help would be MUCH MUCH appreciated.

I need to be able to dynamically populate a drop down list with values obtained from the results of a lookup formula. Is that possible?

The lookup formula should find all instances of a value in a column of a table held in another worksheet. There could be as many as 4 instances in that table, and for each instance, it should lookup all the values in another table column, then auto-populate this into a data validation drop-down box.

I need 2 such drop down boxes for each of 700 rows.

Sample Data:

COST PRICES SHEET[/B][/SIZE]
BRAND CODE COLOR SIZE COST
TOPSPORT TTA01 COL. S,M,L,XL,XXL 1.26
TOPSPORT TTA01 COL. XXXL 1.36
TOPSPORT TTA01 WHT. S,M,L,XL,XXL 1.26
TOPSPORT TTA01 WHT. XXXL 1.36
RUSSELL RTA09 Colors S-XXL 1.44

SALES PRICES SHEET
BRAND CODE COLOR SIZE COST ACTUAL COLOR ACTUAL SIZE
TOPSPORT TTA01 Cols S-XXL ? "WHT." , "COL." "S,M,L,XL,XXL" , "XXXL"
TOPSPORT TTA01 Cols 3XL ? "WHT." , "COL." "S,M,L,XL,XXL" , "XXXL"
TOPSPORT TTA01 White S-XXL ? "WHT." , "COL." "S,M,L,XL,XXL" , "XXXL"
TOPSPORT TTA01 White 3XL ? "WHT." , "COL." "S,M,L,XL,XXL" , "XXXL"


I need to pull the Color and Size options for each Brand/ProductCode combination from the COST PRICES SHEET and populate dropdowns in the SALES PRICES SHEET with these options into 2 columns called 'ACTUAL COLOR' and 'ACTUAL SIZE'.

The user should be able to select which color and size option should apply to that row in the SALE PRICES SHEET, thereby enabling a lookup table to use those options to pull the cost price from the COST PRICES SHEET into the COST column of the SALES PRICES SHEET.

WHY IS THIS NECESSARY?
Because the COST PRICES SHEET contains the Price List information from around 20 different suppliers, each of which have different ways of saying the same thing (eg "White", "WHI.", "Wht","WH" etc). And with around 5000 lines of Cost Prices, all being updated every few weeks, it is impossible to try and standardise their terminology. Hence a lookup from the SALES PRICES SHEET is nigh on impossible.

Download actual Excel file from HERE
(Please ignore the 'Settings Sheet' in this file). Explanatory comments are included in the file but can be deleted if required.

Actual file contains thousands of rows in both sheets. so efficient method would be most useful.

Many thanks.

 

[Somendra Misra]

@Bali Rakhra
Hi!

Welcome to the forum.

Can you please suggest the column refrence from which you want data to be the input of data validation & output cell refrence. Also please give some examples as to what user will select in what cell and what output you are expecting in which cell.

Regards!

 

[Bali Rakhra]

Hi

Input for each data validation drop down is to come from each of:

COST PRICE SHEET, Column 'COLOR'
and
COST PRICE SHEET, Column 'SIZE'

and the dropdowns should be located in:

SALES PRICE SHEET, Column 'ACTUAL COLOR'
and
SALES PRICE SHEET, Column 'ACTUAL SIZE'

In the dropdowns, the user should be able to choose all the possible colours and sizes (as listed in the COST PRICES SHEET) that are available for that product code.

In my original listing, I included ALL those options under the SALES PRICES SHEET 'ACTUAL COLOR' and 'ACTUAL SIZE' columns , but the reality is that these cells would be blank, and would contain the validation dropdowns with the values as options that are dynamically pulled in from the COST PRICES SHEET.

Does that make sense?