Data Validation Lists

[Sue]

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

 

[Gord Dibben]

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

 

[Ashish Mathur]

Hi,

Try this:

1. Select the source data including the header row and convert it to a List
(Ctrl+L);
2. Select the source data excluding the header row and assign it a name
(Ctrl+F3), say dummy;
3. Click on any cell and now validate he cell. In the source box of data
validation, type dummy
4. Now when you add any data to the source range, it would automatically
show up in the validation drop down

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Sue]

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("ReferringReason"),
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]

Where are the other named ranges located?

Same sheet, different columns?

I think you would have to go to a Select Case method of choosing which range
and column to use.


Gord

 

[WallyWallWhackr]

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

Yes. I use a range of cells that are referred to by the validator.

Check this out. You name a range of cells (a single column group) and
use that named range in the drop down list criteria box as =rangename

Example name the range "List1" and use "=List1" in the drop down box.

Now, as you INSERT rows within that original named range, the range
auto-expands.

There are also auto-expanding formulas you can use.

Check out my workbook:

http://office.microsoft.com/en-us/templates/TC300083091033.aspx?CategoryID=CT101172771033