data validation list to automatically adjust to changing list

Hi Folk..

I have a constantly changing list of names in colum I. I use 'data validation' in cell c3 to show the list. How can I get the drop down List in cell C3 to only show the list of names and not all the blanks below it


(I have been trying to create a 'Dynamic Name Range' using office help to no avail,, am I on the right lines for this)

many thanks

Jim

What is the source of list in C3?

EDIT: Oh I saw it (column I). So probably something like this in the validation source line:
=OFFSET(I:I,0,0,COUNTIF(I:I,"<>"""),1)

many thanks for your help guys...

Ron,, The list in column 'I' had cells with equation in them which produced empty cells which then showed in the drop down list, my appologies I didn't know it did this,, (I do now). your hlp set me on the road to discovering the equation in the validation source line..

=OFFSET($A$1,0,0,MATCH("*",$A:$A,-1),1)

many thank this helped me solve my problem

Barbara

you sites look very good,,, I will be using them in the future ,, many thanks for the tip about Ctrl+L to create the dynamic range

We are here to help...

Don't forget to mark your thread solved if we've cleared your doubts.

EDIT: Yehhh! It's solved!