Let's assume the list is on sheet 2 A1:A10:

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)

So how would Biff formula work/look??
Source: =OFFSET($A$1,,,SUMPRODUCT(--(LEN($A$1:$A$10)0)))

Goto InsertNameDefine
Names in workbook: type in: Mylist
Refers to: =OFFSET(Sheet2!$A$1,,,SUMPRODUCT(--(LEN(Sheet2!$A$1:$A$10)0)))
OK

Now, back on Sheet1 (or whatever other sheet) where your drop down is, as
the source for the drop down use =MyList.

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

Yes, I've seen that article. I "used" to write formulas like that too! <bg

The =IF(ROWS($1:1)<=COUNTIF($T$1:$T$62,$W$1) part is just a pseudo error
trap and is much shorter and more efficient than:

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

Biff

"Hayley" wrote in message
...
So how would Biff formula work/look??
Source: =OFFSET($A$1,,,SUMPRODUCT(--(LEN($A$1:$A$10)0)))

--
Hayley


"Peo Sjoblom" wrote:

If you name your list you can use it in another sheet, insertnamedefine
and give it a name like MyList
then refer to it as

=MyList

in the source box

--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com