If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Data Validation Lists
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!! |
#2
|
|||
|
|||
Data Validation Lists
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!! |
#3
|
|||
|
|||
Data Validation Lists
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" wrote in message ... 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!! |
#4
|
|||
|
|||
Data Validation Lists
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!! . |
#5
|
|||
|
|||
Data Validation Lists
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 On Tue, 9 Feb 2010 19:48:01 -0800, Sue wrote: 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!! . |
#6
|
|||
|
|||
Data Validation Lists
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!! 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/te...CT101172771033 |
Thread Tools | |
Display Modes | |
|
|