Back to Excel Tips Index

1. In cell A3 thru A28 type the letters A, B, C,...Z

2. In cell B3 type the formula:

ex.=INDEX(IF(COUNTIF(Rng_Output_List,Rng_Original_List)=0,Rng_Original_List,""),SMALL(IF(Rng_Original_List=IF(COUNTIF(Rng_Output_List,Rng_Original_List)=0,Rng_Original_List,""),ROW($1:$26)),ROW()-2))

Confirm with Ctrl+Shift+Enter (not just Enter) and copy down to cell B28.

3. Go to Insert/Name/Define and define the following Names:

·  Rng_Original_List - Refers to =$A$3:$A$28

·  Rng_Dynamic_List - Refers to =$B$3:$B$28

·  Rng_Output_List - Refers to =$D$3:$D$28

·  myRng - Refers to =INDEX(Rng_Dynamic_List,1):INDEX(Rng_Dynamic_List,MATCH(REPT("z",255),Rng_Dynamic_List))

4. Now insert the validation:

·  Select cells D3 thru D28

·  Go to Data/Validation

·  Select List in the Allow box

·  In Source type the formula =myRng and click Ok.