Dynamic Data Validation list without duplicates

[Ajesh]

Hi all,

Need help in figuring this out -

We have Month names as source in cell A1:A12 as Jan, Feb, Mar...Dec.
Data Validation drop down list needs to be applied in cell C1:C12 wherein if user has selected a month name in any of the cells C1:C12, he/she cannot select the same month in other cells (C1:C12) for e.g. they cannot choose Jan more than once in C1:C12.

I understand this can be done easily via VBA but I am looking for a non-VBA solution.

Appreciate your help on the matter.

 

[NARAYANK991]

Hi ,

Does this help ?

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

Narayan

 

[bosco_yip]

Data Validation dropdown list with remove duplicate values.

1] A2 array formula copied down to A13

=IFERROR(INDEX({"Jan";"Feb";"Mar";"Apr";"May";"Jun";"Jul";"Aug";"Sep";"Oct";"Nov";"Dec"},SMALL(IF(ISNA(MATCH({"Jan";"Feb";"Mar";"Apr";"May";"Jun";"Jul";"Aug";"Sep";"Oct";"Nov";"Dec"},C$2:C$13&"",0)),ROW(A$2:A$13)-ROW(A$2)+1),ROW(A1))),"")

p.s. array formula to be confirmed enter with CTRL+SHIFT+ENTER instead of just ENTER.

2] Select C2:C13 >> Data Validation >>

Allow : List

Source : $A$2:$A$13

>> OK

3] See attached file

Regards
Bosco

 

[Ajesh]

Hi ,

Does this help ?

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

Narayan

Thanks Narayan - worked like a charm.

 

[Ajesh]

Data Validation dropdown list with remove duplicate values.

1] A2 array formula copied down to A13

=IFERROR(INDEX({"Jan";"Feb";"Mar";"Apr";"May";"Jun";"Jul";"Aug";"Sep";"Oct";"Nov";"Dec"},SMALL(IF(ISNA(MATCH({"Jan";"Feb";"Mar";"Apr";"May";"Jun";"Jul";"Aug";"Sep";"Oct";"Nov";"Dec"},C$2:C$13&"",0)),ROW(A$2:A$13)-ROW(A$2)+1),ROW(A1))),"")

p.s. array formula to be confirmed enter with CTRL+SHIFT+ENTER instead of just ENTER.

2] Select C2:C13 >> Data Validation >>

Allow : List

Source : $A$2:$A$13

>> OK

3] See attached file

Regards
Bosco

Thanks bosco - This also works. But there's just one caveat - it adds blank options to the subsequent drop down list. If someone is OK with that - it is in fact simpler to set up and works perfectly. However, I used the example from Contextures as suggested by @NARAYANK991 .

Thanks to you anyway. Much appreciated.

 

[bosco_yip]

Thanks bosco - This also works. But there's just one caveat - it adds blank options to the subsequent drop down list. If someone is OK with that - it is in fact simpler to set up and works perfectly. However, I used the example from Contextures as suggested by @NARAYANK991 .

Thanks to you anyway. Much appreciated.

Hi,

1] It can be fixed by adding a dynamic range name for the Validation List to remove the blank option.

2] See attached revised file.

Regards
Bosco

 

[Ajesh]

Hi,

1] It can be fixed by adding a dynamic range name for the Validation List to remove the blank option.

2] See attached revised file.

Regards
Bosco

Awesome - right on target!!!

Thanks Bosco for your valuable time and efforts.