Thanks Biff!!!!!!!!!

Works a treat when i put it in the source field. I was trying to put it in
the name field and wasn't working. However my reference list is on another
sheet so validation dosn't allow this however I can lock down the first sheet
and hid the list there.

Your shortened formaula also works. I have NO idea what it all means but it
works. I found the first one at
http://office.microsoft.com/en-us/as...260381033.aspx

--
Thanks again, much appreciated. Hayley


"Biff" wrote:

That's the formula you are using as the source for your drop down list?

That formula only returns a single value.

I think we're not understanding each other!

Assume you have a range of values that you want to use as the source for a
data validation drop down list. That range includes formula blanks in
several cells at the end of the range. This range is A1:A10 and looks
something like this:

A1 = Joe
A2 = Mary
A3 = Lisa
A4 = Tom
A5 = Karen
A6 = "" (formula blank)
A7 = "" (formula blank)
A8 = "" (formula blank)
A9 = "" (formula blank)
A10 = "" (formula blank)

To use this range as the source for a drop down:

Select the cell where you want the drop down.
Goto DataValidation
Allow: List
Source: =OFFSET($A$1,,,SUMPRODUCT(--(LEN($A$1:$A$10)0)))
OK out

Your drop down list will not contain any blank selections.

=IF(ISERROR(INDEX($T$1:$U$62,SMALL(IF($T$1:$T$62=$ W$1,ROW($T$1:$T$62)),ROW(3:3)),2)),"",INDEX($T$1:$ U$62,SMALL(IF($T$1:$T$62=$W$1,ROW($T$1:$T$62)),ROW (3:3)),2))

While I'm at it, that formula can be shortened to: (if my hunch is correct!)

=IF(ROWS($1:1)<=COUNTIF($T$1:$T$62,$W$1),INDEX($U$ 1:$U$62,SMALL(IF($T$1:$T$62=$W$1,ROW($T$1:$T$62)), ROW(3:3))),"")

Biff

"Hayley" wrote in message
...
Blank cells are at the end of the range.

The formula in the 'name' range was the same as Debra's suggestion (with
columns changed). The formula in the select cell is as below

=IF(ISERROR(INDEX($T$1:$U$62,SMALL(IF($T$1:$T$62=$ W$1,ROW($T$1:$T$62)),ROW(3:3)),2)),"",INDEX($T$1:$ U$62,SMALL(IF($T$1:$T$62=$W$1,ROW($T$1:$T$62)),ROW (3:3)),2))
--
Hayley


"Biff" wrote:

Hi!

Are the blanks cells scattered within the range or are they all at the
end
of the range?

Did you try Debra's suggestion of using a dynamic range? If so, what does
you formula look like?

Biff