drop down list showing only 1 of everything

[kishan]

Hi everyone, basically on one sheet( called data) i have
a list of all the data, the first column is the date. On
a separate sheet i want to have a drop down list in the
cell (A1 for example).

however i want the drop down list to show only 1 of each
date that was in column A of the data sheet.

for example say column A had the folowing dates: 1/1/04 ;
2/1/04 ; 2/1/04 ; 3/1/04 ; 4/1/04 ; 4/1/04

the drop down list on sheet 2 would show the values:
1/1/04 ; 2/1/04 ; 3/4/04 ; 4/4/04

i need some help in doing this and would appreciate any
ideas. thank you....

 

[Frank Kabel]

Hi
do you need this formula to be dynamic?. and what kind of drop down do
you want to use? Data validation listbox

 

[kishan]

It depends what you mean by dynamic, the formula would
have to expand the area if a row was inserted in the
list. also yes i was hoping to use the data validation
tool for the list box. thanks for the quick reply...

 

[Frank Kabel]

Hi
o.k. then lets assume the following:
- sheet 1, column A contains your list of dates
- on sheet 1, column B enter the following formulas:
B1: =A1
B2: enter the array formula (entered with CTRL+SHIFT+ENTER):
=IF(ISERROR(INDEX($A$1:$A$100,MATCH(0,COUNTIF(B$1:B1,$A$1:$A$100),0))),
"",INDEX($A$1:$A$100,MATCH(0,COUNTIF(B$1:B1,$A$1:$A$100),0)))
and copy this down for all rows

- Now goto 'Insert - Name - Define' and enter a name (e.g.
'unique_list') and the following formula:
=OFFSET($B$1,0,0,LOOKUP(2,1/($B$1:$B$100<>""),ROW($B$1:$B$100)))

Now on your second sheet goto 'Data - Validation' and enter the
following data source for your list:
=unique_list

 

[kishan]

thank you very much - that worked great and solved my
problem. thanks again.