Don't you want to use the values in column B for the selections in the drop
down list?

If you can sort your data on column A so that it looks like this:

......A...............B......
HN305.HN305-1
HN305.HN305-2
HN305.HN305-3
HN306.HN306-1
HN306.HN306-2
HN306.HN306-3
HN306.HN306-4
HN307.HN307-1
HN307.HN307-2
HN307.HN307-3

Then, a the source for the drop down:

=OFFSET(B1,MATCH("HN305",A1:A10,0)-1,,COUNTIF(A1:A10,"HN305"))


--
Biff
Microsoft Excel MVP


"Vinod" wrote in message
...
Hi All,

Is it possible to give array formulae as source reference for a cell
datavalidation list?

Note: using formulae
Eg: {=IF(UPPER(A1:A10)=UPPER("HN305"),INDEX(A1:A10,ROW (A1:A10)),"")}

ColA ColB
HN305 HN305-1
HN305 HN305-2
HN306 HN306-1
HN307 HN307-1
HN305 HN305-3
HN306 HN306-2
HN306 HN306-3
HN307 HN307-2
HN307 HN307-3
HN306 HN306-4

I tried with this formulae:
=IF(UPPER(A1:A10)=UPPER("HN305"),INDEX(A1:A10,ROW( A1:A10)),"")

When I've give it in datavalidation list soure and clicked on OK button I
got the message "The source currently evaluates to an error. Do you want
to
continue?". If I click on YES nothing is dispalyed in dropdown list.

Is there any another logic to be displayed in dropdown which are
corresponding 'HN305'?

Please help me out.

Advanced Thanks,
Vinod