Hi Larry,

I'm having a similar problem.

My dynamic list works in that it expands as I add more to it, providing I
enter it inthe next available cell in the list - as you would expect.
However, I wish to use this list as a data source for validation on another
sheet in my workbook. When I enter the list name (i.e. = location), I get
the same message that you did, about the range name not being found. It does
not appear on the range list, nor when I press F3, but is in the Insert,
Name, Define list. I wondered how you were getting on with this, and if you
could offer any help.

Thanks Larry.

Take care,

Karen.

"Larry" wrote:

Roger, I appreciate the help. I have done as you suggested.
I set up a workbook called 1986,
I set up various worksheets for each auto in inventory by vehicle number.
I also made an additional sheet called list.
On this sheet I have two lists, cloumn A is "descrepency" and column B is
Initial. Column B is o.k because it is a static list at this point and works
as long as I name the range and use data validation for each column of each
sheet that I have set up.

The problem for me is the Descrepency list which I would like to be dynamic
and self updating as you indicated.
I created the list named it Descrepency.
When I go to inset/name/define and put the folllowing in "refers to"
=OFFSET($A$1,0,0,COUNTA($A:$A))
The name descrepency disappears from the list of named ranges.
Each worksheet with validation to the descrepency list shows a drop down
with all the entries listed, and a selection can be made.
If an entry is made that is not on the list it will post but the list is not
updating nor expanding as expected.

whaddayya think? thanks man. larry

"Roger Govier" wrote:

Hi Larry

To set up a dynamic range for your list using the Offset() function, you
need to do the following (assuming your list is in column A, starting in
row 1 and could be of any length, as you add new data.

InsertNameDefine
In the Name pane at the top of the dialogue enter Initials
In the Refers to pane at the bottom enter
=OFFSET($A$1,0,0,COUNTA($A:$A))
Next Click Add

If you have done it correctly, clicking on the icon at the end of the
Refers to pane, should highlight the range of cells that the Name
Initials refers to.

How Offset works
$A$1 is the reference point
,0,0 is saying use an offset of 0 Rows and 0 Columns from this cell as
the start position - which is still A1
,COUNTA(A:A) counts the number of non-blank cells in column A, and uses
this as the depth of the range.

I have omitted the final parameter in the formula, which therefore cause
it to default to 1, as your list is only one column wide, but you could
have a final , and number (or , and variable) which would define a range
which had a number of columns width.

If you had entries in cells A1 down to A12, CountA() would return 12,
and the range would get defined as A1:A12.
As soon as you enter a new value in your list, CountA() would increase
to 13, and the range would grow to A1:A13

I hope this aids your understanding and allows you to create the ranges
you want.

--
Regards

Roger Govier


"Larry" wrote in message
...
I'm having a terrible time getting something to work. I want two lists
that
will automatically update when a new item is added that is not in the
list.

I have a work book, representing a year. In the workbook are several
sheets,
representing autos. In the workbook I have created one sheet that has
two
lists in it, one for initials (Initial) and one for recording
damages(Descrepency). I have named the lists and set the target clumns
in the
auto sheets to validate to the lists.
I read Debra Dalgleish's contextures on dynamic ranges but when I
follow the
guidance there and insert the if= in defined name, the list names
disappear
and the worksheets return an error that no list name can be found. I
have
done everything my limited knowledge allows but have gotten nowhere
with
this. I got some code from a fine gentleman for lists but it also has
failed
to allow me to auto update, always returning some error.
I love this bulletin, but so far none of the recommended solutions out
there
are working, I feel I am missing some simple step here. Help??