Thanks Roger,
I wish I had more VBA skills, I only know how to record a macro. I have
picked up a few things here though, and plan on taking some courses, VBA is
pretty handy, though frustrating at times. I know I am missing some very
simple things. I have been all through the contextures page and this
bulletin. I have tried many things and am close, I now have the drop down
that I want. I added a combo box to the page but it still will not show more
than 8 lines.
when I follow the guidance for creating a dynamic list, it indicates that
new items will be added to the list but in no circumstance for me has it done
so.
the list I need to create will have a large number of entries made. A
standard list of terms will help, but new items should be added to the list
once typed in.
Do you have this code for creating an auto updating list? I sure appreciate
your assistance. larry

"Roger Govier" wrote:

Hi Larry

I think that you are misunderstanding the Dynamic List.
If you enter a new value on one of your sheets, it will not
automatically (or dynamically) add itself to your named range.
You need to add the entry in column A below the last entry, then it will
appear in your dropdown list whenever you next use it, from any of your
sheets.

There are methods for adding to Lists, but only via code written in VBA.

--
Regards

Roger Govier


"Larry" wrote in message
...
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??