Excel : populate data validation dropdown list using a single formula that gets multiple values from lookup with multiple criteria

Question

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 data validation drop down list with values obtained from the results of a lookup formula. Is that possible?

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

Sample Data:

Color and size data is to be pulled FROM this sheet:

COST PRICES SHEET

BRAND CODE ACTUAL COLOR ACTUAL 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

and to be used as values in dropdowns in cells marked [*1] and [*2] in the following worksheet :

SALES PRICES SHEET

BRAND CODE COLOR SIZE COST ACTUAL COLOR ACTUAL SIZE

TOPSPORT TTA01      Cols        S-XXL             [?]                      [*1]                                        [*2]
TOPSPORT TTA01      Cols        3XL                 [?]                      [*1]                                        [*2]
TOPSPORT TTA01      White       S-XXL            [?]                      [*1]                                        [*2]
TOPSPORT TTA01      White        3XL               [?]                      [*1]                                        [*2]

    So, according to the above example...
    Cells marked [*1] should house a dropdown with options of "WHT." and "COL."
    Cells marked [*2] should house a dropdown with options of "S,M,L,XL,XXL" and "XXXL"
    Cells marked [?] will use these dropdown values in a lookup formula 
                     to pull in the Cost Price from COST PRICES SHEET.

THE QUESTION...

Is there some formula I could use in the Data Validation formula box which can do this? If not, is there any other way to populate the dropdowns?

WHY IS ALL THIS NECESSARY?

Because the COST PRICES SHEET contains Price information from dozens of different suppliers, each of which have different ways of saying the same thing (eg for "White","WHIT", "WHIT.", "Wht","WH" etc). Hence a simple lookup from the SALES PRICES SHEET is not possible, as the sales sheet uses a standard string to define a term (eg "White") which would fail to find the various spellings used by the different suppliers.

So, if the specific values used by each supplier for all color /size options for a product were available in the SALE PRICES SHEET, the user could then select the color and size option which applies to that record, thereby enabling a lookup formula to use those values to pull the appropriate cost price from the COST PRICES SHEET into the COST column of the SALES PRICES SHEET.

And with around 5000 lines of Cost Prices being updated every month, it's not feasible to manually standardise the various spellings used.

Download actual Excel file from HERE

(ignore the 'Settings Sheet' in the download file).

What exactly is your question? (If it's "can someone write me the code for this" then you have come to the wrong place...) — chris neilsen
Sorry if it's too text heavy. (The background info is there just in case what I'm asking for is irrelevant because someone can see a completely different way of achieving the same end result. Anyway, the actual question would be: How can a data validation dropdown list be dynamically populated by ALL the multiple results from a lookup formula that uses 2 criteria? Does that make more sense? PS. I've coded 95% of this, but just stuck on this final issue: — bali rakhra
A little - its a bit TLDR. I would suggest you clean up your Q (remove the back story - or at least push it to the bottom as optional reading, add some sample data, add what you have tried, describe succinctly what you want to achieve, and make sure it's on topic) As it stands it risks being closed. — chris neilsen
Better, but I'd like to see some sample data in the Question. I see two possibilities here: use a dynamic named range (lots of examples on SO and generally on the web, good start here ) or write a VBA UDF that returns the validation list as an array, using the UDF as the validation source. — chris neilsen

sten sten · Accepted Answer · 2014-03-07T17:14:11

You're looking for something like this. Dynamic Data Validation I have something like this in a sheet I run at my office. However I agree with Chris that VBA will probably be a more robust solution long term.