I think you would need to build a contiguous list (no blank cells) in another
range to do this. As I recall, Named ranges with complex formulas don't work
well in data validation.

you can use a formula like

=IF(COUNTA($F$1:$F$50)=ROW(),OFFSET($F$1,SMALL(IF ($F$1:$F$50<"",ROW($F$1:$F$50)),ROW())-1,0),"")

to get a list with no blanks.

This is an array formula and would be entered with Ctrl+Shift+enter rather
than just enter. then drag fill down the column (entered in G1 and filled
down in the example)

Then your dynamic range would be

Name: List1

Refersto: =Offset(Sheet1!$G$1,0,0,counta(Sheet1!$G$1:$G$50), 1)

where G1 and down is where you formula is placed.

--
Regards,
Tom Ogilvy

"Harry Stevens" wrote:

To All,
I have a named range that includes some blank cells that I am trying
to eliminate from a drop down list on another worksheet. I have Googled
the internet and searched through several Excel newsgroups and think the
solution is the use of INDIRECT and Dynamic ranges, except using COUNTA
stops at the first blank cell.

Can someone point me in the right direction with either a Formula or
VBA that will take a named range (I would like a Dynamic range - if
possible), strip out the blank cells to use with a drop down data
validation list.

Thanks
Harry