Create drop down list based on various user entered data.

[dlr00]

Hello -

I need to create a drop down list populated with data based on pre-selected fields. Any help is much appreciated!!

Sheet1
A1 - drop down list with two choices (red, blue)
A2 - drop down list with five choices (1,2,3,4,5)
A3 - drop down list with four choices (w,x,y,z)
A4 - **I need a drop down list that returns the values of a column (Sheet2 - Description), when the choices in cells A1, A2, and A3 are present in the adjoining columns on Sheet2.

list-multiple-data.xlsx

I don't want to use VBA.


Thanks!!

[rylo]

Hi

This gets messy, but will get you there.

Sheet2:
E2: =IF((B2=Sheet1!$A$1)*(Sheet2!C2=Sheet1!$A$2)*(Sheet2!D2=Sheet1!$A$3),ROW(),"") (this is array entered and copied down to E16)
G1: =COUNT(E:E). Select this cell, and create a named range called cntr
F1: =INDEX(A:A,SMALL(E:E,ROW()))
Copy down as required.
Now comes a repetitive part. Select F1, and create a names range called desc1. Then select F1:F2, and create a named range called desc2. Continue down to cover however many cells in column F that contain a formula.

Sheet1:

A4: Data Validation, list, and the source is =INDIRECT("desc"&cntr)

If there are no valid entries, then the drop down should not show any entries. If there are results, then they should appear.

HTH

rylo