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,
--
Joe

VBA Automation/VB/C++/Web and DB development

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

-----Original Message-----
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,
--
Joe

VBA Automation/VB/C++/Web and DB development
.

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

On Tue, 17 Aug 2004 10:03:01 -0700, Joe wrote:

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,

Use the offset function to define a dynamic range.

Say your data is on Sheet1, A1:A10

From Insert:Name:Define:

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

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


-----Original Message-----
Nils,

This isn't quite what I am looking for. The problem that
I need to solve is
this:

If I have a list of 5 items in a column and I name the
range that contains
the columns, myNamedRange, when I add a value to the list
(so that now I have
6 items), how do I dynamically update the RefersTo
property of the
myNamedRange to include the newest value in the list?

Also if the list is reduced to four items, how do I
dynamically update the
RefersTo property of myNamedRange to only include the
four items and not show
a blank for the deleted fifth item?

Thanks,

Joe

" wrote:

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

-----Original Message-----
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,
--
Joe

VBA Automation/VB/C++/Web and DB development
.


.