Auto update (extend) Name range

[hocs]

I have defined a Name called 'Fruit' ranging from cell A1 to A3, say:
A1 = Apple
A2 = Orange
A3 = Banana
and i'll use this Name 'Fruit' as data validation (list) on sheet2 (source: =Fruit).
If i were to add on A4 = Kiwi, A5 = Peach and so on.. Can the Name range 'Fuit' be extended automatically whenever a new item is added?
I know i can simply name the range from A1:A1000 but this will show lots of irrelavent blanks when i chose from validation drop down selector at sheet2.
Please advise.
Thanks!

 

[Randombard]

Hi There,

You are talking about a dynamic range

Named ranges can contain formula's and by useing the offset function you can produce exactly what you are after.

So in your named range "Fruit" enter the formula

=OFFSET($A$1,,,COUNTA($A:$A))

Modify as needed.

I hope this helps

 

[somnath_it2006]

You can check/download the same example at below link:

http://www.ozgrid.com/News/excel-validation-tips.htm

 

[hocs]

It works well!
Thanks both!