Drop-down list based on another cell value

2016-02-16_195805.png

Simple question maybe, help pls.
Column A (not unique values), Column B (unique values).

In F and G columns need dropdown list, based on A and B columns values. If category 1 (1st drop-down) then can choose one from all values for that category (drop-down 2).

Looks simple, but in column A values are not unique... And it not comfortably
And I need to avoid any helper columns.

Perhaps this might help..

http://www.contextures.com/xlDataVal02.html

Assuming your Drop Downs are in F2 and G2-

First you need to create two helper columns to extract the required data.

In this example I am using Column J and Column K as the Helper Columns.

So in cell J2 use this formula-

=IF(IFERROR(INDEX($A$2:$A$100, MATCH(0,COUNTIF($J$1:J1, $A$2:$A$100), 0)),"")=0,"",IFERROR(INDEX($A$2:$A$100, MATCH(0,COUNTIF($J$1:J1, $A$2:$A$100), 0)),""))

and in cell K2-

=IFERROR(INDEX($B:$B,SMALL(IF($A$2:$A$100=$F$2,ROW($B$2:$B$100),""),ROW(A1))),"")

Now create two drop-downs with range J2:J till end and K2:K till end..

Check attached for reference.

Glad to know that helped!!
If your query is resolved, please take a moment to mark this thread as "Solved" from the "Thread Tools" at the top of this page.

Regards