Dependent drop down list (one column has duplicates)

[Naveen N]

Dear Reader,

I have column C (month which has duplicates) and column D which has week off (start and end date of week).

I need to create a dependent drop down list in another sheet.

With first list with Month name (duplicates removed) and second dependent list listing week off for the month.

Please suggest how to create the dependent drop down.

Please find enclosed sample file.

Regard,

Naveen N

 

[bobhc]

Probably the best site going for data validation.

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


.

 

[bosco_yip]

Try………

1] Create a unique Month name list, by copy >> paste special + value…. from Column C to Column F and Data >> Remove Duplicates >> OK

2] Define name >>

Name : Weekoff

Refer to : =OFFSET('Dependent values'!$C$1,MATCH($H2,'Dependent values'!$C$2:$C$64,0),1,COUNTIF('Dependent values'!$C$2:$C$64,$H2))

3] 1st dependent list with Month name >> H2 >> Data >> Data Validation >>

Allow : List

Source : =$F$2:$F$13

Copy down

4] 2nd dependent list with Weekoff name >> I2 >> Data >> Data Validation >>

Allow : List

Source : =Weekoff

Copy down

Regards
Bosco