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

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.

Insert>Name>Define
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" <(E-Mail Removed)> wrote in message
news:687F26DA-A366-4F35-8F80-(E-Mail Removed)...
> 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??

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.
>
> Insert>Name>Define
> 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" <(E-Mail Removed)> wrote in message
> news:687F26DA-A366-4F35-8F80-(E-Mail Removed)...
> > 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??
>
>
>

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" <(E-Mail Removed)> wrote in message
news:7C9937A4-13D3-44A6-AEA9-(E-Mail Removed)...
> 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.
>>
>> Insert>Name>Define
>> 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" <(E-Mail Removed)> wrote in message
>> news:687F26DA-A366-4F35-8F80-(E-Mail Removed)...
>> > 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??
>>
>>
>>

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" <(E-Mail Removed)> wrote in message
> news:7C9937A4-13D3-44A6-AEA9-(E-Mail Removed)...
> > 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.
> >>
> >> Insert>Name>Define
> >> 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" <(E-Mail Removed)> wrote in message
> >> news:687F26DA-A366-4F35-8F80-(E-Mail Removed)...
> >> > 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??
> >>
> >>
> >>
>
>
>

Hi Roger,
I found this handy bit of code I managed to tweat to do what I have been
after.

IN MODULE 1 I HAVE:
Option Explicit
Sub Workbook_Open()
Columns("B:B").Select
Range("B3").Activate
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="WAIVER%20NO.xls", _
TextToDisplay:=""
End If
Else
End If
End Sub
NO SHEET 1OR2

IN SHEET 3 I HAVE:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Columns(1).Sort Key1:=Range("A1"), Order1:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom
End Sub

IN SHEET 4 I HAVE:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
Dim ws As Worksheet
Dim i As Integer
Set ws = Worksheets("Lists")
If Target.Column = 3 And Target.Row > 1 Then
If Application.WorksheetFunction.CountIf(ws.Range("NameList"),
Target.Value) Then
Exit Sub
Else
i = ws.Cells(Rows.Count, 1).End(xlUp).Row + 1
ws.Range("A" & i).Value = Target.Value
ws.Range("NameList").Sort Key1:=ws.Range("A1"), _
Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
End If
End If
End Sub

I WANT TO ADD THE FOLLOWING CODE FOR DATE ENTRY BUT HAVE NOT BEEN ABLE TO
GET IT WORK WITH EITHER OF THE TWO SHEETS. HELP??

If ActiveCell.Column = 3 Then 'Limits macro action to column C
If ActiveCell.Value = "" Then 'Check to see if Target cell empty
Selection.Value = Date 'Insert today's date in Target cell
Else
End If
Else
End If
End Sub


***************************************
"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

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.
> >
> > Insert>Name>Define
> > 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" <(E-Mail Removed)> wrote in message
> > news:687F26DA-A366-4F35-8F80-(E-Mail Removed)...
> > > 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??
> >
> >
> >

Hi Larry,

If this one works for you.

Assuming you have data from A2 to A100 and the sheet is activated till
100th row. Name the range A2:A5000 as "myRange" and then at the List,
type =myRange. There will few more technical things come your way, but
till time this will work perfectly for you.

Thanks

Shail


Larry wrote:
> 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??

Hi Karen, sorry it took so long to answer I've been away a few days. My list
is working pretty good now, thinks to a lot of help from others.
I have a workbook that is one of many representing years, each has many
sheets representing many vehicles, each with various repair and damage
reports. each report needs its own, unique number.
My workbook is setup with sheet 1 as "Lists", sheet2 . . . set up as the
inpu sheet auto number "xxx". the Lists contains two rows, one with
descrepencies the other with initials. Initials is a static list made with
data validation. Descrepency list has a nice auto-updating drop down that ads
every new entry to the next empty row. the date column is auto entry.
Here is the code:
sheet"Lists" code (auto updating list): click the sheet tab and view code
and paste


Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Columns(1).Sort Key1:=Range("A1"), _
Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom
End Sub
+++++++++++++++++++++++++++++++++++++++++++++
Each input list for the vehicles has this code:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
Dim ws As Worksheet
Dim i As Integer
Set ws = Worksheets("Lists")
If Target.Column = 1 And Target.Row > 1 Then
If Application.WorksheetFunction.CountIf(ws.Range("NameList"),
Target.Value) Then
Exit Sub
Else
i = ws.Cells(Rows.Count, 1).End(xlUp).Row + 1
ws.Range("A" & i).Value = Target.Value
ws.Range("NameList").Sort Key1:=ws.Range("A1"), _
Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom

End If
Else
End If
End Sub
+++++++++++++++++++++++++++++++++++++++
Immediately below the aboce is this code to automatically enter the date:

Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
'Automatically inserts today's date in cell in column C when selected
'if the cell was empty. Does not overwrite occupied cell.
If ActiveCell.Column = 3 Then 'Limits macro action to column C
If ActiveCell.Value = "" Then 'Check to see if Target cell empty
Selection.Value = Date 'Insert today's date in Target cell
End If
Else
End If
End Sub
+++++++++++++++++++++++++++++++
that's it just be sure the lists are in the correct columns, if you want a
sample write to:
(E-Mail Removed) and I will sent it to you. take care


"KarenF" wrote:

> 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.
> > >
> > > Insert>Name>Define
> > > 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" <(E-Mail Removed)> wrote in message
> > > news:687F26DA-A366-4F35-8F80-(E-Mail Removed)...
> > > > 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??
> > >
> > >
> > >