Hi! Here's my situation... In a worksheet, within a column, repetitive data
can be entered. So to speed things along, i've created lists, and then
applied data validation to the column. My question is how can i get the drop
down lists to automatically expand when a new choice (one not in the current
list) is entered, and then be a choice from there on? In my creation of the
list i have used the OFFSET fxn, and if i type the new choice in the list
worksheet, the dropdown list automatically updates. What i'd like is for it
to be entered on the building spreadsheet and then update to the list
worksheet and be a choice in the dropdown list from that point. Thanks for
any help you might be able to give!!!! mary trueblood :-)

If you're using xl97, this won't work.

But if you're using xl2k or better, you could use a worksheet event that updates
that other list.

Rightclick on the worksheet tab that should have this behavior. Select view
code and paste this in:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myVRng As Range
Dim res As Variant

If Target.Cells.Count 1 Then Exit Sub
If Intersect(Target, Me.Range("e1:e30")) Is Nothing Then Exit Sub

Set myVRng = Worksheets("sheet2").Range("mylist")

res = Application.Match(Target.Value, myVRng, 0)
If IsError(res) Then
'not on the list yet
With myVRng
.Cells(1).Offset(.Rows.Count, 0).Value = Target.Value
With .Resize(.Rows.Count + 1, 1)
.Sort key1:=.Columns(1), order1:=xlAscending, header:=xlNo
End With
End With
End If

End Sub

I put my list in Sheet2: A1:A?? (no header!)
I used this formula in insert|define to create that name "myList":
=OFFSET(Sheet2!$A$1,0,0,COUNTA(Sheet2!$A:$A),1)

I made the Data|validation error alert a warning with this text in the message:

If you accept this entry, it will be added to the Data|Validation list for this
range.



metrueblood wrote:

Hi! Here's my situation... In a worksheet, within a column, repetitive data
can be entered. So to speed things along, i've created lists, and then
applied data validation to the column. My question is how can i get the drop
down lists to automatically expand when a new choice (one not in the current
list) is entered, and then be a choice from there on? In my creation of the
list i have used the OFFSET fxn, and if i type the new choice in the list
worksheet, the dropdown list automatically updates. What i'd like is for it
to be entered on the building spreadsheet and then update to the list
worksheet and be a choice in the dropdown list from that point. Thanks for
any help you might be able to give!!!! mary trueblood :-)

--

Dave Peterson