Variable Length List

sam_d1

Board Regular
Joined
Mar 17, 2002
Messages
50
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
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

anno

Board Regular
Joined
Feb 16, 2002
Messages
202
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

Board Regular
Joined
Mar 17, 2002
Messages
50
Thanks Anno,
It works...
I am amazed by your Faaaaast response
thanks
- sam
 

sam_d1

Board Regular
Joined
Mar 17, 2002
Messages
50
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

Board Regular
Joined
Feb 16, 2002
Messages
202

ADVERTISEMENT

you have to be pretty quick these days to beat paddyd :biggrin:

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

Board Regular
Joined
Mar 17, 2002
Messages
50
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

Board Regular
Joined
Feb 16, 2002
Messages
202
hi sam
not sure what that's about - have never come across that help topic before. anyway, glad it worked for you.
cheers
anno
 

Forum statistics

Threads
1,143,742
Messages
5,720,596
Members
422,292
Latest member
Bernd0501

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top