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
.
.