Dynamic Validation Lists

[Guest]

I am using XL 2002. In my workbook I have a sheet that stores the values
used for validation lists. One such list is SalesReps. This is a dynamic
list. If I use a named range for the values in the list, and the list either
grows or shrinks, I will need to use a formula for the RefersTo property so
that when this list is referred to in data validation it will be accurate.

Does anyone know how to code this or what formula I could use to make the
RefersTo dynamic?

TIA,

 

[Guest]

Hey
I have build an dynamic validation list by using the
lookup-formula in the cells wich are in use for the
validation.
By building stuctures like this:

Level 1 contains the main-categories (example
1. Course
2. HelpDesk
3. Development
.... etc

Level 2 contains the underdoing issues like:
1. Excel Advanced
2. Word MOUSexpert
.... etc

By using lookup on the value in level 1, my formula
respons the level 2 ussues in the range.

If this means any sence to your problem, I can make a
specific sample and mail it to you

Regards
Nils Morten Blakstad
ConsEasy

 

[Gord Dibben]

Joe

Debra Dalgleish has instructions for this at.........

http://www.contextures.on.ca/xlNames01.html#Dynamic

Also check out her info on Data Validation lists.

http://www.contextures.on.ca/xlDataVal01.html

Gord Dibben Excel MVP

 

[DJH]

Use the offset function to define a dynamic range.

Say your data is on Sheet1, A1:A10

From Insert:Nameefine:

Fill in your data's Name and then in the REFERS TO Use

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)