Variable Length List

[sam_d1]

I have a Named List (say City_List in cells A1:A10)
Number of Cities listed under this list is variable (max of 10)
I use this 'City_List' to validate other cells. (Data/Validation/List)

When I have fewer ( <10 )cities listed, my validation dropdown box shows blank entries.

I want the dropdown list to grow or shrink as the list changes.

How do I do this ?

Thanks
- sam

 

[anno]

sam
set the validation criteria to allow: list, and the source as =OFFSET($A$1,0,0,COUNT($A:$A),1) for numeric values or =OFFSET($A$1,0,0,COUNTA($A:$A),1) for alpha values, where A1 has the first list entry.

 

[sam_d1]

Thanks Anno,
It works...
I am amazed by your Faaaaast response
thanks
- sam

 

[sam_d1]

Oops...
This works in the same sheet...
But my List are in a different sheet...
Any Idea how do it across different sheets..

thanks
- sam

 

[anno]

you have to be pretty quick these days to beat paddyd

i'm wishing i hadn't beaten him to the punch though. hmm...not sure , but i think this will work.

use the formula i gave you to create a dynamic named range for your validation list - insert>name>define> and put the formula in the 'refers to' box. then for your data validation 'source' box, put '=yourrangename' without the quotes.
does that make sense?
This message was edited by anno on 2002-09-24 00:35

 

[sam_d1]

Hi Anno
Cool... It works...
Thankyou very much...

Competition Really Helps...

Meanwhile I was going through the excel help.
I found the following text under 'Contents'/'Validating Cell Entries'/ 'Specify the valid entries for cells'/'Restrict cell entries to the data from a list'.

But this does not seem to work. (unless I create the Named List as you have explained...
Any Idea ?

Text from excel help...
If the list of valid entries might change, name the list range, and then enter the name in the Source box. When the named range grows or shrinks because of changes you make to the list on the worksheet, the list of valid entries for the cell automatically reflects the changes


thanks
- sam

 

[anno]

hi sam
not sure what that's about - have never come across that help topic before. anyway, glad it worked for you.
cheers
anno