Thanks for the response. I am very new to all of the coding. I have some more
questions related to my first posting. I have a "Lists" spreadsheet that has
a number of named ranges in it. My first named range "ReferringReason" pulls
correctly into my drop-down list and I am able to free-text other criteria in
the cells that will then add to my drop-down list. However, I have other
named ranges that I need to pull into other drop-down lists. How do I
replicate the code so that it will work? I assume that I need to change the
Target Column and the Named Range (ex. ReferringProvider) . I keep getting
error messages. On my January spreadsheet, I have the following codes set up:

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 = 5 And Target.Row 1 Then
If Application.WorksheetFunction.CountIf(ws.Range("Re ferringReason"),
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("ReferringReason").Sort Key1:=ws.Range("A1"), _
Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
End If
End If

End Sub


Thank you so much!!

"Gord Dibben" wrote:

Download a sample workbook from Debra Dalgleish's site.

http://www.contextures.on.ca/excelfiles.html#DataVal

Scroll down to DV0012 and download the workbook.

DV0012 - Update Validation List -- type a new value in a cell that contains
data validation, and it's automatically added to the source list, and the
list is sorted; a macro automates the list updates.


Gord Dibben MS Excel MVP

On Mon, 8 Feb 2010 15:05:01 -0800, Sue
wrote:

I have a question regarding the drop downs lists created using Data
Validation. Is there any way to make them so that when I type something new
into the box that it is automatically added to the drop down list? Thank you!!