Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excell Dropdown List. Display alternate text than found in list.
I have a form that uses a dropdown list to show employee names. What I want
to do is allow the names to be selected in the dropdown list but the employee number to actually be inserted into the form. Is this even an option? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excell Dropdown List. Display alternate text than found in list.
You could use some code to replace the name with the id number.
But I would would use an adjacent cell so that I could display both. If A1 contained the name (a cell with data|validation???), then in B1: =if(a1="","",vlookup(a1,sheet2!a:b,2,false) Where Sheet2 contained the list of names in column A and the ID's in column B. But if you want, Debra Dalgleish has a sample workbook he http://contextures.com/excelfiles.html#DataVal Look for: DV0004 - Data Validation Change -- Select a Product from the Data Validation list; an event procedure changes the product name to a product code. DataValCode.zip 8 kb Shawnn wrote: I have a form that uses a dropdown list to show employee names. What I want to do is allow the names to be selected in the dropdown list but the employee number to actually be inserted into the form. Is this even an option? -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excell Dropdown List. Display alternate text than found in li
I would use an adjacent cell to make this project much easier on me.
Unfortunately I have to follow strict state reporting guidelines. With that being said, I have tried for several hours to duplicate the example in the download you provided but I keep getting errors. The example is exactly what I want to do though. Do you know of a step by step guide? "Dave Peterson" wrote: You could use some code to replace the name with the id number. But I would would use an adjacent cell so that I could display both. If A1 contained the name (a cell with data|validation???), then in B1: =if(a1="","",vlookup(a1,sheet2!a:b,2,false) Where Sheet2 contained the list of names in column A and the ID's in column B. But if you want, Debra Dalgleish has a sample workbook he http://contextures.com/excelfiles.html#DataVal Look for: DV0004 - Data Validation Change -- Select a Product from the Data Validation list; an event procedure changes the product name to a product code. DataValCode.zip 8 kb Shawnn wrote: I have a form that uses a dropdown list to show employee names. What I want to do is allow the names to be selected in the dropdown list but the employee number to actually be inserted into the form. Is this even an option? -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excell Dropdown List. Display alternate text than found in li
Nope.
Maybe you could share some details. The address of the range that holds the names and the id's. The cell that's getting the data|validation. And the code that you tried. Shawnn wrote: I would use an adjacent cell to make this project much easier on me. Unfortunately I have to follow strict state reporting guidelines. With that being said, I have tried for several hours to duplicate the example in the download you provided but I keep getting errors. The example is exactly what I want to do though. Do you know of a step by step guide? "Dave Peterson" wrote: You could use some code to replace the name with the id number. But I would would use an adjacent cell so that I could display both. If A1 contained the name (a cell with data|validation???), then in B1: =if(a1="","",vlookup(a1,sheet2!a:b,2,false) Where Sheet2 contained the list of names in column A and the ID's in column B. But if you want, Debra Dalgleish has a sample workbook he http://contextures.com/excelfiles.html#DataVal Look for: DV0004 - Data Validation Change -- Select a Product from the Data Validation list; an event procedure changes the product name to a product code. DataValCode.zip 8 kb Shawnn wrote: I have a form that uses a dropdown list to show employee names. What I want to do is allow the names to be selected in the dropdown list but the employee number to actually be inserted into the form. Is this even an option? -- Dave Peterson -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excell Dropdown List. Display alternate text than found in li
I have several areas that require a code similar to a state abbreviation.
For example the word Statutory Burglary mast be recorded as SB. So I have set up multiple colums on Sheet2. A1:A10 are titles, B1:B10 are the abreaviations. The next is C1:C10 are titles and D1:D10 are abbreviations and so on. I used the DV dropdown in my document cell N8 (which is a merged cell) to show the list from A1:A10. Once the ull name is selected I need it to show the abbreviation in the form. I need this for many areas in my report. I used the following code. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Then GoTo exitHandler If Target.Column = 14 Then If Target.Value = "" Then GoTo exitHandler Application.EnableEvents = False Target.Value = Worksheets("Sheet2").Range("B1") _ .Offset(Application.WorksheetFunction _ .Match(Target.Value, Worksheets("Sheet2").Range("DID"), 0), 0) End If exitHandler: Application.EnableEvents = True Exit Sub End Sub I would be happy to send you a copy if it will help. "Dave Peterson" wrote: Nope. Maybe you could share some details. The address of the range that holds the names and the id's. The cell that's getting the data|validation. And the code that you tried. Shawnn wrote: I would use an adjacent cell to make this project much easier on me. Unfortunately I have to follow strict state reporting guidelines. With that being said, I have tried for several hours to duplicate the example in the download you provided but I keep getting errors. The example is exactly what I want to do though. Do you know of a step by step guide? "Dave Peterson" wrote: You could use some code to replace the name with the id number. But I would would use an adjacent cell so that I could display both. If A1 contained the name (a cell with data|validation???), then in B1: =if(a1="","",vlookup(a1,sheet2!a:b,2,false) Where Sheet2 contained the list of names in column A and the ID's in column B. But if you want, Debra Dalgleish has a sample workbook he http://contextures.com/excelfiles.html#DataVal Look for: DV0004 - Data Validation Change -- Select a Product from the Data Validation list; an event procedure changes the product name to a product code. DataValCode.zip 8 kb Shawnn wrote: I have a form that uses a dropdown list to show employee names. What I want to do is allow the names to be selected in the dropdown list but the employee number to actually be inserted into the form. Is this even an option? -- Dave Peterson -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excell Dropdown List. Display alternate text than found in li
First, merged cells cause nothing but grief. I do my best to avoid them.
But I merged N8:P8 and this seemed to work ok for me. I also named the range List (not DID). In xl2007, DID will look like a column, so I wouldn't use that. Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim res As Variant If Target.Cells.Count 1 Then Exit Sub End If If Intersect(Target, Me.Range("N8")) Is Nothing Then Exit Sub End If If Target.Value = "" Then Exit Sub End If Application.EnableEvents = False With Worksheets("Sheet2") res = Application.Match(Target.Value, .Range("List"), 0) If IsError(res) Then MsgBox "Design error!" 'this shouldn't happen Else Target.Value = .Range("list").Offset(0, 1)(res).Value End If End With Application.EnableEvents = True End Sub Shawnn wrote: I have several areas that require a code similar to a state abbreviation. For example the word Statutory Burglary mast be recorded as SB. So I have set up multiple colums on Sheet2. A1:A10 are titles, B1:B10 are the abreaviations. The next is C1:C10 are titles and D1:D10 are abbreviations and so on. I used the DV dropdown in my document cell N8 (which is a merged cell) to show the list from A1:A10. Once the ull name is selected I need it to show the abbreviation in the form. I need this for many areas in my report. I used the following code. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Then GoTo exitHandler If Target.Column = 14 Then If Target.Value = "" Then GoTo exitHandler Application.EnableEvents = False Target.Value = Worksheets("Sheet2").Range("B1") _ .Offset(Application.WorksheetFunction _ .Match(Target.Value, Worksheets("Sheet2").Range("DID"), 0), 0) End If exitHandler: Application.EnableEvents = True Exit Sub End Sub I would be happy to send you a copy if it will help. "Dave Peterson" wrote: Nope. Maybe you could share some details. The address of the range that holds the names and the id's. The cell that's getting the data|validation. And the code that you tried. Shawnn wrote: I would use an adjacent cell to make this project much easier on me. Unfortunately I have to follow strict state reporting guidelines. With that being said, I have tried for several hours to duplicate the example in the download you provided but I keep getting errors. The example is exactly what I want to do though. Do you know of a step by step guide? "Dave Peterson" wrote: You could use some code to replace the name with the id number. But I would would use an adjacent cell so that I could display both. If A1 contained the name (a cell with data|validation???), then in B1: =if(a1="","",vlookup(a1,sheet2!a:b,2,false) Where Sheet2 contained the list of names in column A and the ID's in column B. But if you want, Debra Dalgleish has a sample workbook he http://contextures.com/excelfiles.html#DataVal Look for: DV0004 - Data Validation Change -- Select a Product from the Data Validation list; an event procedure changes the product name to a product code. DataValCode.zip 8 kb Shawnn wrote: I have a form that uses a dropdown list to show employee names. What I want to do is allow the names to be selected in the dropdown list but the employee number to actually be inserted into the form. Is this even an option? -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excell Dropdown List. Display alternate text than found in li
Ok I will try that. In the mean time... how do i do this for multiple cells
and lists? Lets just say I have 10 different areas i need to use this code. How would I set that up? "Dave Peterson" wrote: First, merged cells cause nothing but grief. I do my best to avoid them. But I merged N8:P8 and this seemed to work ok for me. I also named the range List (not DID). In xl2007, DID will look like a column, so I wouldn't use that. Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim res As Variant If Target.Cells.Count 1 Then Exit Sub End If If Intersect(Target, Me.Range("N8")) Is Nothing Then Exit Sub End If If Target.Value = "" Then Exit Sub End If Application.EnableEvents = False With Worksheets("Sheet2") res = Application.Match(Target.Value, .Range("List"), 0) If IsError(res) Then MsgBox "Design error!" 'this shouldn't happen Else Target.Value = .Range("list").Offset(0, 1)(res).Value End If End With Application.EnableEvents = True End Sub Shawnn wrote: I have several areas that require a code similar to a state abbreviation. For example the word Statutory Burglary mast be recorded as SB. So I have set up multiple colums on Sheet2. A1:A10 are titles, B1:B10 are the abreaviations. The next is C1:C10 are titles and D1:D10 are abbreviations and so on. I used the DV dropdown in my document cell N8 (which is a merged cell) to show the list from A1:A10. Once the ull name is selected I need it to show the abbreviation in the form. I need this for many areas in my report. I used the following code. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Then GoTo exitHandler If Target.Column = 14 Then If Target.Value = "" Then GoTo exitHandler Application.EnableEvents = False Target.Value = Worksheets("Sheet2").Range("B1") _ .Offset(Application.WorksheetFunction _ .Match(Target.Value, Worksheets("Sheet2").Range("DID"), 0), 0) End If exitHandler: Application.EnableEvents = True Exit Sub End Sub I would be happy to send you a copy if it will help. "Dave Peterson" wrote: Nope. Maybe you could share some details. The address of the range that holds the names and the id's. The cell that's getting the data|validation. And the code that you tried. Shawnn wrote: I would use an adjacent cell to make this project much easier on me. Unfortunately I have to follow strict state reporting guidelines. With that being said, I have tried for several hours to duplicate the example in the download you provided but I keep getting errors. The example is exactly what I want to do though. Do you know of a step by step guide? "Dave Peterson" wrote: You could use some code to replace the name with the id number. But I would would use an adjacent cell so that I could display both. If A1 contained the name (a cell with data|validation???), then in B1: =if(a1="","",vlookup(a1,sheet2!a:b,2,false) Where Sheet2 contained the list of names in column A and the ID's in column B. But if you want, Debra Dalgleish has a sample workbook he http://contextures.com/excelfiles.html#DataVal Look for: DV0004 - Data Validation Change -- Select a Product from the Data Validation list; an event procedure changes the product name to a product code. DataValCode.zip 8 kb Shawnn wrote: I have a form that uses a dropdown list to show employee names. What I want to do is allow the names to be selected in the dropdown list but the employee number to actually be inserted into the form. Is this even an option? -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excell Dropdown List. Display alternate text than found in li
I'd try changing this line:
If Intersect(Target, Me.Range("N8")) Is Nothing Then to something like: If Intersect(Target, Me.Range("N8:N18,L22:m25,x15")) Is Nothing Then Shawnn wrote: Ok I will try that. In the mean time... how do i do this for multiple cells and lists? Lets just say I have 10 different areas i need to use this code. How would I set that up? "Dave Peterson" wrote: First, merged cells cause nothing but grief. I do my best to avoid them. But I merged N8:P8 and this seemed to work ok for me. I also named the range List (not DID). In xl2007, DID will look like a column, so I wouldn't use that. Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim res As Variant If Target.Cells.Count 1 Then Exit Sub End If If Intersect(Target, Me.Range("N8")) Is Nothing Then Exit Sub End If If Target.Value = "" Then Exit Sub End If Application.EnableEvents = False With Worksheets("Sheet2") res = Application.Match(Target.Value, .Range("List"), 0) If IsError(res) Then MsgBox "Design error!" 'this shouldn't happen Else Target.Value = .Range("list").Offset(0, 1)(res).Value End If End With Application.EnableEvents = True End Sub Shawnn wrote: I have several areas that require a code similar to a state abbreviation. For example the word Statutory Burglary mast be recorded as SB. So I have set up multiple colums on Sheet2. A1:A10 are titles, B1:B10 are the abreaviations. The next is C1:C10 are titles and D1:D10 are abbreviations and so on. I used the DV dropdown in my document cell N8 (which is a merged cell) to show the list from A1:A10. Once the ull name is selected I need it to show the abbreviation in the form. I need this for many areas in my report. I used the following code. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Then GoTo exitHandler If Target.Column = 14 Then If Target.Value = "" Then GoTo exitHandler Application.EnableEvents = False Target.Value = Worksheets("Sheet2").Range("B1") _ .Offset(Application.WorksheetFunction _ .Match(Target.Value, Worksheets("Sheet2").Range("DID"), 0), 0) End If exitHandler: Application.EnableEvents = True Exit Sub End Sub I would be happy to send you a copy if it will help. "Dave Peterson" wrote: Nope. Maybe you could share some details. The address of the range that holds the names and the id's. The cell that's getting the data|validation. And the code that you tried. Shawnn wrote: I would use an adjacent cell to make this project much easier on me. Unfortunately I have to follow strict state reporting guidelines. With that being said, I have tried for several hours to duplicate the example in the download you provided but I keep getting errors. The example is exactly what I want to do though. Do you know of a step by step guide? "Dave Peterson" wrote: You could use some code to replace the name with the id number. But I would would use an adjacent cell so that I could display both. If A1 contained the name (a cell with data|validation???), then in B1: =if(a1="","",vlookup(a1,sheet2!a:b,2,false) Where Sheet2 contained the list of names in column A and the ID's in column B. But if you want, Debra Dalgleish has a sample workbook he http://contextures.com/excelfiles.html#DataVal Look for: DV0004 - Data Validation Change -- Select a Product from the Data Validation list; an event procedure changes the product name to a product code. DataValCode.zip 8 kb Shawnn wrote: I have a form that uses a dropdown list to show employee names. What I want to do is allow the names to be selected in the dropdown list but the employee number to actually be inserted into the form. Is this even an option? -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excell Dropdown List. Display alternate text than found in li
Ok I think I follow you so far... but wouldn't I have to put the ten
different "list" in as well? For example list1, list2 list3 and so on? "Dave Peterson" wrote: I'd try changing this line: If Intersect(Target, Me.Range("N8")) Is Nothing Then to something like: If Intersect(Target, Me.Range("N8:N18,L22:m25,x15")) Is Nothing Then Shawnn wrote: Ok I will try that. In the mean time... how do i do this for multiple cells and lists? Lets just say I have 10 different areas i need to use this code. How would I set that up? "Dave Peterson" wrote: First, merged cells cause nothing but grief. I do my best to avoid them. But I merged N8:P8 and this seemed to work ok for me. I also named the range List (not DID). In xl2007, DID will look like a column, so I wouldn't use that. Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim res As Variant If Target.Cells.Count 1 Then Exit Sub End If If Intersect(Target, Me.Range("N8")) Is Nothing Then Exit Sub End If If Target.Value = "" Then Exit Sub End If Application.EnableEvents = False With Worksheets("Sheet2") res = Application.Match(Target.Value, .Range("List"), 0) If IsError(res) Then MsgBox "Design error!" 'this shouldn't happen Else Target.Value = .Range("list").Offset(0, 1)(res).Value End If End With Application.EnableEvents = True End Sub Shawnn wrote: I have several areas that require a code similar to a state abbreviation. For example the word Statutory Burglary mast be recorded as SB. So I have set up multiple colums on Sheet2. A1:A10 are titles, B1:B10 are the abreaviations. The next is C1:C10 are titles and D1:D10 are abbreviations and so on. I used the DV dropdown in my document cell N8 (which is a merged cell) to show the list from A1:A10. Once the ull name is selected I need it to show the abbreviation in the form. I need this for many areas in my report. I used the following code. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Then GoTo exitHandler If Target.Column = 14 Then If Target.Value = "" Then GoTo exitHandler Application.EnableEvents = False Target.Value = Worksheets("Sheet2").Range("B1") _ .Offset(Application.WorksheetFunction _ .Match(Target.Value, Worksheets("Sheet2").Range("DID"), 0), 0) End If exitHandler: Application.EnableEvents = True Exit Sub End Sub I would be happy to send you a copy if it will help. "Dave Peterson" wrote: Nope. Maybe you could share some details. The address of the range that holds the names and the id's. The cell that's getting the data|validation. And the code that you tried. Shawnn wrote: I would use an adjacent cell to make this project much easier on me. Unfortunately I have to follow strict state reporting guidelines. With that being said, I have tried for several hours to duplicate the example in the download you provided but I keep getting errors. The example is exactly what I want to do though. Do you know of a step by step guide? "Dave Peterson" wrote: You could use some code to replace the name with the id number. But I would would use an adjacent cell so that I could display both. If A1 contained the name (a cell with data|validation???), then in B1: =if(a1="","",vlookup(a1,sheet2!a:b,2,false) Where Sheet2 contained the list of names in column A and the ID's in column B. But if you want, Debra Dalgleish has a sample workbook he http://contextures.com/excelfiles.html#DataVal Look for: DV0004 - Data Validation Change -- Select a Product from the Data Validation list; an event procedure changes the product name to a product code. DataValCode.zip 8 kb Shawnn wrote: I have a form that uses a dropdown list to show employee names. What I want to do is allow the names to be selected in the dropdown list but the employee number to actually be inserted into the form. Is this even an option? -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excell Dropdown List. Display alternate text than found in li
Yep.
Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim res As Variant Dim myAddr As Variant Dim myLists As Variant Dim ThisRng As Range Dim ThisList As String Dim iCtr As Long 'Each address matches up with the corresponding list name 'so be careful. myAddr = Array("a1:a10", "b3:c9", "e5") myLists = Array("List1", "List2", "List3") If UBound(myAddr) < UBound(myLists) Then MsgBox "Design error!" Exit Sub End If If Target.Cells.Count 1 Then Exit Sub End If If Target.Value = "" Then Exit Sub End If Set ThisRng = Nothing For iCtr = LBound(myAddr) To UBound(myAddr) If Intersect(Target, Me.Range(myAddr(iCtr))) Is Nothing Then 'keep looking Else Set ThisRng = Me.Range(myAddr(iCtr)) ThisList = myLists(iCtr) Exit For 'stop looking End If Next iCtr If ThisRng Is Nothing Then 'change is in a "non-important" cell Exit Sub End If Application.EnableEvents = False With Worksheets("Sheet2") res = Application.Match(Target.Value, .Range(ThisList), 0) If IsError(res) Then MsgBox "Design error!" 'this shouldn't happen Else Target.Value = .Range(ThisList).Offset(0, 1)(res).Value End If End With Application.EnableEvents = True End Sub Shawnn wrote: Ok I think I follow you so far... but wouldn't I have to put the ten different "list" in as well? For example list1, list2 list3 and so on? "Dave Peterson" wrote: I'd try changing this line: If Intersect(Target, Me.Range("N8")) Is Nothing Then to something like: If Intersect(Target, Me.Range("N8:N18,L22:m25,x15")) Is Nothing Then Shawnn wrote: Ok I will try that. In the mean time... how do i do this for multiple cells and lists? Lets just say I have 10 different areas i need to use this code. How would I set that up? "Dave Peterson" wrote: First, merged cells cause nothing but grief. I do my best to avoid them. But I merged N8:P8 and this seemed to work ok for me. I also named the range List (not DID). In xl2007, DID will look like a column, so I wouldn't use that. Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim res As Variant If Target.Cells.Count 1 Then Exit Sub End If If Intersect(Target, Me.Range("N8")) Is Nothing Then Exit Sub End If If Target.Value = "" Then Exit Sub End If Application.EnableEvents = False With Worksheets("Sheet2") res = Application.Match(Target.Value, .Range("List"), 0) If IsError(res) Then MsgBox "Design error!" 'this shouldn't happen Else Target.Value = .Range("list").Offset(0, 1)(res).Value End If End With Application.EnableEvents = True End Sub Shawnn wrote: I have several areas that require a code similar to a state abbreviation. For example the word Statutory Burglary mast be recorded as SB. So I have set up multiple colums on Sheet2. A1:A10 are titles, B1:B10 are the abreaviations. The next is C1:C10 are titles and D1:D10 are abbreviations and so on. I used the DV dropdown in my document cell N8 (which is a merged cell) to show the list from A1:A10. Once the ull name is selected I need it to show the abbreviation in the form. I need this for many areas in my report. I used the following code. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Then GoTo exitHandler If Target.Column = 14 Then If Target.Value = "" Then GoTo exitHandler Application.EnableEvents = False Target.Value = Worksheets("Sheet2").Range("B1") _ .Offset(Application.WorksheetFunction _ .Match(Target.Value, Worksheets("Sheet2").Range("DID"), 0), 0) End If exitHandler: Application.EnableEvents = True Exit Sub End Sub I would be happy to send you a copy if it will help. "Dave Peterson" wrote: Nope. Maybe you could share some details. The address of the range that holds the names and the id's. The cell that's getting the data|validation. And the code that you tried. Shawnn wrote: I would use an adjacent cell to make this project much easier on me. Unfortunately I have to follow strict state reporting guidelines. With that being said, I have tried for several hours to duplicate the example in the download you provided but I keep getting errors. The example is exactly what I want to do though. Do you know of a step by step guide? "Dave Peterson" wrote: You could use some code to replace the name with the id number. But I would would use an adjacent cell so that I could display both. If A1 contained the name (a cell with data|validation???), then in B1: =if(a1="","",vlookup(a1,sheet2!a:b,2,false) Where Sheet2 contained the list of names in column A and the ID's in column B. But if you want, Debra Dalgleish has a sample workbook he http://contextures.com/excelfiles.html#DataVal Look for: DV0004 - Data Validation Change -- Select a Product from the Data Validation list; an event procedure changes the product name to a product code. DataValCode.zip 8 kb Shawnn wrote: I have a form that uses a dropdown list to show employee names. What I want to do is allow the names to be selected in the dropdown list but the employee number to actually be inserted into the form. Is this even an option? -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excell Dropdown List. Display alternate text than found in li
First, thanks for all the help! Next, I got the multiple cells to work as
long as they are using the same list. I am having a problem getting other cells to do it for different lists. Here is my code::: Private Sub Worksheet_Change(ByVal Target As Range) Dim res As Variant If Target.Cells.Count 1 Then Exit Sub End If If Intersect(Target, Me.Range("N7, AE7")) Is Nothing Then Exit Sub End If If Target.Value = "" Then Exit Sub End If Application.EnableEvents = False With Worksheets("Sheet2") res = Application.Match(Target.Value, .Range("Weekday"), 0) If IsError(res) Then MsgBox "Design error!" 'this shouldn't happen Else Target.Value = .Range("Weekday").Offset(0, 1)(res).Value End If End With Application.EnableEvents = True End Sub Private Sub object_Change() Dim res As Variant If Target.Cells.Count 1 Then Exit Sub End If If Intersect(Target, Me.Range("AI9")) Is Nothing Then Exit Sub End If If Target.Value = "" Then Exit Sub End If Application.EnableEvents = False With Worksheets("Sheet2") res = Application.Match(Target.Value, .Range("Security"), 0) If IsError(res) Then MsgBox "Design error!" 'this shouldn't happen Else Target.Value = .Range("Security").Offset(0, 1)(res).Value End If End With Application.EnableEvents = True End Sub As you can see, I want AI9 to read from the list called security, not weekday. I will need to repeat this for another 20 or so cells... any advise? "Dave Peterson" wrote: I'd try changing this line: If Intersect(Target, Me.Range("N8")) Is Nothing Then to something like: If Intersect(Target, Me.Range("N8:N18,L22:m25,x15")) Is Nothing Then Shawnn wrote: Ok I will try that. In the mean time... how do i do this for multiple cells and lists? Lets just say I have 10 different areas i need to use this code. How would I set that up? "Dave Peterson" wrote: First, merged cells cause nothing but grief. I do my best to avoid them. But I merged N8:P8 and this seemed to work ok for me. I also named the range List (not DID). In xl2007, DID will look like a column, so I wouldn't use that. Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim res As Variant If Target.Cells.Count 1 Then Exit Sub End If If Intersect(Target, Me.Range("N8")) Is Nothing Then Exit Sub End If If Target.Value = "" Then Exit Sub End If Application.EnableEvents = False With Worksheets("Sheet2") res = Application.Match(Target.Value, .Range("List"), 0) If IsError(res) Then MsgBox "Design error!" 'this shouldn't happen Else Target.Value = .Range("list").Offset(0, 1)(res).Value End If End With Application.EnableEvents = True End Sub Shawnn wrote: I have several areas that require a code similar to a state abbreviation. For example the word Statutory Burglary mast be recorded as SB. So I have set up multiple colums on Sheet2. A1:A10 are titles, B1:B10 are the abreaviations. The next is C1:C10 are titles and D1:D10 are abbreviations and so on. I used the DV dropdown in my document cell N8 (which is a merged cell) to show the list from A1:A10. Once the ull name is selected I need it to show the abbreviation in the form. I need this for many areas in my report. I used the following code. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Then GoTo exitHandler If Target.Column = 14 Then If Target.Value = "" Then GoTo exitHandler Application.EnableEvents = False Target.Value = Worksheets("Sheet2").Range("B1") _ .Offset(Application.WorksheetFunction _ .Match(Target.Value, Worksheets("Sheet2").Range("DID"), 0), 0) End If exitHandler: Application.EnableEvents = True Exit Sub End Sub I would be happy to send you a copy if it will help. "Dave Peterson" wrote: Nope. Maybe you could share some details. The address of the range that holds the names and the id's. The cell that's getting the data|validation. And the code that you tried. Shawnn wrote: I would use an adjacent cell to make this project much easier on me. Unfortunately I have to follow strict state reporting guidelines. With that being said, I have tried for several hours to duplicate the example in the download you provided but I keep getting errors. The example is exactly what I want to do though. Do you know of a step by step guide? "Dave Peterson" wrote: You could use some code to replace the name with the id number. But I would would use an adjacent cell so that I could display both. If A1 contained the name (a cell with data|validation???), then in B1: =if(a1="","",vlookup(a1,sheet2!a:b,2,false) Where Sheet2 contained the list of names in column A and the ID's in column B. But if you want, Debra Dalgleish has a sample workbook he http://contextures.com/excelfiles.html#DataVal Look for: DV0004 - Data Validation Change -- Select a Product from the Data Validation list; an event procedure changes the product name to a product code. DataValCode.zip 8 kb Shawnn wrote: I have a form that uses a dropdown list to show employee names. What I want to do is allow the names to be selected in the dropdown list but the employee number to actually be inserted into the form. Is this even an option? -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excell Dropdown List. Display alternate text than found in li
Only what I suggested before.
Shawnn wrote: First, thanks for all the help! Next, I got the multiple cells to work as long as they are using the same list. I am having a problem getting other cells to do it for different lists. Here is my code::: Private Sub Worksheet_Change(ByVal Target As Range) Dim res As Variant If Target.Cells.Count 1 Then Exit Sub End If If Intersect(Target, Me.Range("N7, AE7")) Is Nothing Then Exit Sub End If If Target.Value = "" Then Exit Sub End If Application.EnableEvents = False With Worksheets("Sheet2") res = Application.Match(Target.Value, .Range("Weekday"), 0) If IsError(res) Then MsgBox "Design error!" 'this shouldn't happen Else Target.Value = .Range("Weekday").Offset(0, 1)(res).Value End If End With Application.EnableEvents = True End Sub Private Sub object_Change() Dim res As Variant If Target.Cells.Count 1 Then Exit Sub End If If Intersect(Target, Me.Range("AI9")) Is Nothing Then Exit Sub End If If Target.Value = "" Then Exit Sub End If Application.EnableEvents = False With Worksheets("Sheet2") res = Application.Match(Target.Value, .Range("Security"), 0) If IsError(res) Then MsgBox "Design error!" 'this shouldn't happen Else Target.Value = .Range("Security").Offset(0, 1)(res).Value End If End With Application.EnableEvents = True End Sub As you can see, I want AI9 to read from the list called security, not weekday. I will need to repeat this for another 20 or so cells... any advise? "Dave Peterson" wrote: I'd try changing this line: If Intersect(Target, Me.Range("N8")) Is Nothing Then to something like: If Intersect(Target, Me.Range("N8:N18,L22:m25,x15")) Is Nothing Then Shawnn wrote: Ok I will try that. In the mean time... how do i do this for multiple cells and lists? Lets just say I have 10 different areas i need to use this code. How would I set that up? "Dave Peterson" wrote: First, merged cells cause nothing but grief. I do my best to avoid them. But I merged N8:P8 and this seemed to work ok for me. I also named the range List (not DID). In xl2007, DID will look like a column, so I wouldn't use that. Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim res As Variant If Target.Cells.Count 1 Then Exit Sub End If If Intersect(Target, Me.Range("N8")) Is Nothing Then Exit Sub End If If Target.Value = "" Then Exit Sub End If Application.EnableEvents = False With Worksheets("Sheet2") res = Application.Match(Target.Value, .Range("List"), 0) If IsError(res) Then MsgBox "Design error!" 'this shouldn't happen Else Target.Value = .Range("list").Offset(0, 1)(res).Value End If End With Application.EnableEvents = True End Sub Shawnn wrote: I have several areas that require a code similar to a state abbreviation. For example the word Statutory Burglary mast be recorded as SB. So I have set up multiple colums on Sheet2. A1:A10 are titles, B1:B10 are the abreaviations. The next is C1:C10 are titles and D1:D10 are abbreviations and so on. I used the DV dropdown in my document cell N8 (which is a merged cell) to show the list from A1:A10. Once the ull name is selected I need it to show the abbreviation in the form. I need this for many areas in my report. I used the following code. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Then GoTo exitHandler If Target.Column = 14 Then If Target.Value = "" Then GoTo exitHandler Application.EnableEvents = False Target.Value = Worksheets("Sheet2").Range("B1") _ .Offset(Application.WorksheetFunction _ .Match(Target.Value, Worksheets("Sheet2").Range("DID"), 0), 0) End If exitHandler: Application.EnableEvents = True Exit Sub End Sub I would be happy to send you a copy if it will help. "Dave Peterson" wrote: Nope. Maybe you could share some details. The address of the range that holds the names and the id's. The cell that's getting the data|validation. And the code that you tried. Shawnn wrote: I would use an adjacent cell to make this project much easier on me. Unfortunately I have to follow strict state reporting guidelines. With that being said, I have tried for several hours to duplicate the example in the download you provided but I keep getting errors. The example is exactly what I want to do though. Do you know of a step by step guide? "Dave Peterson" wrote: You could use some code to replace the name with the id number. But I would would use an adjacent cell so that I could display both. If A1 contained the name (a cell with data|validation???), then in B1: =if(a1="","",vlookup(a1,sheet2!a:b,2,false) Where Sheet2 contained the list of names in column A and the ID's in column B. But if you want, Debra Dalgleish has a sample workbook he http://contextures.com/excelfiles.html#DataVal Look for: DV0004 - Data Validation Change -- Select a Product from the Data Validation list; an event procedure changes the product name to a product code. DataValCode.zip 8 kb Shawnn wrote: I have a form that uses a dropdown list to show employee names. What I want to do is allow the names to be selected in the dropdown list but the employee number to actually be inserted into the form. Is this even an option? -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excell Dropdown List. Display alternate text than found in li
sorry i did not see that post.... I am working on it now. Thanks Bro.
"Dave Peterson" wrote: Only what I suggested before. Shawnn wrote: First, thanks for all the help! Next, I got the multiple cells to work as long as they are using the same list. I am having a problem getting other cells to do it for different lists. Here is my code::: Private Sub Worksheet_Change(ByVal Target As Range) Dim res As Variant If Target.Cells.Count 1 Then Exit Sub End If If Intersect(Target, Me.Range("N7, AE7")) Is Nothing Then Exit Sub End If If Target.Value = "" Then Exit Sub End If Application.EnableEvents = False With Worksheets("Sheet2") res = Application.Match(Target.Value, .Range("Weekday"), 0) If IsError(res) Then MsgBox "Design error!" 'this shouldn't happen Else Target.Value = .Range("Weekday").Offset(0, 1)(res).Value End If End With Application.EnableEvents = True End Sub Private Sub object_Change() Dim res As Variant If Target.Cells.Count 1 Then Exit Sub End If If Intersect(Target, Me.Range("AI9")) Is Nothing Then Exit Sub End If If Target.Value = "" Then Exit Sub End If Application.EnableEvents = False With Worksheets("Sheet2") res = Application.Match(Target.Value, .Range("Security"), 0) If IsError(res) Then MsgBox "Design error!" 'this shouldn't happen Else Target.Value = .Range("Security").Offset(0, 1)(res).Value End If End With Application.EnableEvents = True End Sub As you can see, I want AI9 to read from the list called security, not weekday. I will need to repeat this for another 20 or so cells... any advise? "Dave Peterson" wrote: I'd try changing this line: If Intersect(Target, Me.Range("N8")) Is Nothing Then to something like: If Intersect(Target, Me.Range("N8:N18,L22:m25,x15")) Is Nothing Then Shawnn wrote: Ok I will try that. In the mean time... how do i do this for multiple cells and lists? Lets just say I have 10 different areas i need to use this code. How would I set that up? "Dave Peterson" wrote: First, merged cells cause nothing but grief. I do my best to avoid them. But I merged N8:P8 and this seemed to work ok for me. I also named the range List (not DID). In xl2007, DID will look like a column, so I wouldn't use that. Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim res As Variant If Target.Cells.Count 1 Then Exit Sub End If If Intersect(Target, Me.Range("N8")) Is Nothing Then Exit Sub End If If Target.Value = "" Then Exit Sub End If Application.EnableEvents = False With Worksheets("Sheet2") res = Application.Match(Target.Value, .Range("List"), 0) If IsError(res) Then MsgBox "Design error!" 'this shouldn't happen Else Target.Value = .Range("list").Offset(0, 1)(res).Value End If End With Application.EnableEvents = True End Sub Shawnn wrote: I have several areas that require a code similar to a state abbreviation. For example the word Statutory Burglary mast be recorded as SB. So I have set up multiple colums on Sheet2. A1:A10 are titles, B1:B10 are the abreaviations. The next is C1:C10 are titles and D1:D10 are abbreviations and so on. I used the DV dropdown in my document cell N8 (which is a merged cell) to show the list from A1:A10. Once the ull name is selected I need it to show the abbreviation in the form. I need this for many areas in my report. I used the following code. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Then GoTo exitHandler If Target.Column = 14 Then If Target.Value = "" Then GoTo exitHandler Application.EnableEvents = False Target.Value = Worksheets("Sheet2").Range("B1") _ .Offset(Application.WorksheetFunction _ .Match(Target.Value, Worksheets("Sheet2").Range("DID"), 0), 0) End If exitHandler: Application.EnableEvents = True Exit Sub End Sub I would be happy to send you a copy if it will help. "Dave Peterson" wrote: Nope. Maybe you could share some details. The address of the range that holds the names and the id's. The cell that's getting the data|validation. And the code that you tried. Shawnn wrote: I would use an adjacent cell to make this project much easier on me. Unfortunately I have to follow strict state reporting guidelines. With that being said, I have tried for several hours to duplicate the example in the download you provided but I keep getting errors. The example is exactly what I want to do though. Do you know of a step by step guide? "Dave Peterson" wrote: You could use some code to replace the name with the id number. But I would would use an adjacent cell so that I could display both. If A1 contained the name (a cell with data|validation???), then in B1: =if(a1="","",vlookup(a1,sheet2!a:b,2,false) Where Sheet2 contained the list of names in column A and the ID's in column B. But if you want, Debra Dalgleish has a sample workbook he http://contextures.com/excelfiles.html#DataVal Look for: DV0004 - Data Validation Change -- Select a Product from the Data Validation list; an event procedure changes the product name to a product code. DataValCode.zip 8 kb Shawnn wrote: I have a form that uses a dropdown list to show employee names. What I want to do is allow the names to be selected in the dropdown list but the employee number to actually be inserted into the form. Is this even an option? -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excell Dropdown List. Display alternate text than found in li
OK thank you sooo much that worked great! Last issue.. the dropdown list is
only as wide as the cell. Is there a way to keep the cell size but force the dropdown list to be as wide as the text inside? "Shawnn" wrote: sorry i did not see that post.... I am working on it now. Thanks Bro. "Dave Peterson" wrote: Only what I suggested before. Shawnn wrote: First, thanks for all the help! Next, I got the multiple cells to work as long as they are using the same list. I am having a problem getting other cells to do it for different lists. Here is my code::: Private Sub Worksheet_Change(ByVal Target As Range) Dim res As Variant If Target.Cells.Count 1 Then Exit Sub End If If Intersect(Target, Me.Range("N7, AE7")) Is Nothing Then Exit Sub End If If Target.Value = "" Then Exit Sub End If Application.EnableEvents = False With Worksheets("Sheet2") res = Application.Match(Target.Value, .Range("Weekday"), 0) If IsError(res) Then MsgBox "Design error!" 'this shouldn't happen Else Target.Value = .Range("Weekday").Offset(0, 1)(res).Value End If End With Application.EnableEvents = True End Sub Private Sub object_Change() Dim res As Variant If Target.Cells.Count 1 Then Exit Sub End If If Intersect(Target, Me.Range("AI9")) Is Nothing Then Exit Sub End If If Target.Value = "" Then Exit Sub End If Application.EnableEvents = False With Worksheets("Sheet2") res = Application.Match(Target.Value, .Range("Security"), 0) If IsError(res) Then MsgBox "Design error!" 'this shouldn't happen Else Target.Value = .Range("Security").Offset(0, 1)(res).Value End If End With Application.EnableEvents = True End Sub As you can see, I want AI9 to read from the list called security, not weekday. I will need to repeat this for another 20 or so cells... any advise? "Dave Peterson" wrote: I'd try changing this line: If Intersect(Target, Me.Range("N8")) Is Nothing Then to something like: If Intersect(Target, Me.Range("N8:N18,L22:m25,x15")) Is Nothing Then Shawnn wrote: Ok I will try that. In the mean time... how do i do this for multiple cells and lists? Lets just say I have 10 different areas i need to use this code. How would I set that up? "Dave Peterson" wrote: First, merged cells cause nothing but grief. I do my best to avoid them. But I merged N8:P8 and this seemed to work ok for me. I also named the range List (not DID). In xl2007, DID will look like a column, so I wouldn't use that. Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim res As Variant If Target.Cells.Count 1 Then Exit Sub End If If Intersect(Target, Me.Range("N8")) Is Nothing Then Exit Sub End If If Target.Value = "" Then Exit Sub End If Application.EnableEvents = False With Worksheets("Sheet2") res = Application.Match(Target.Value, .Range("List"), 0) If IsError(res) Then MsgBox "Design error!" 'this shouldn't happen Else Target.Value = .Range("list").Offset(0, 1)(res).Value End If End With Application.EnableEvents = True End Sub Shawnn wrote: I have several areas that require a code similar to a state abbreviation. For example the word Statutory Burglary mast be recorded as SB. So I have set up multiple colums on Sheet2. A1:A10 are titles, B1:B10 are the abreaviations. The next is C1:C10 are titles and D1:D10 are abbreviations and so on. I used the DV dropdown in my document cell N8 (which is a merged cell) to show the list from A1:A10. Once the ull name is selected I need it to show the abbreviation in the form. I need this for many areas in my report. I used the following code. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Then GoTo exitHandler If Target.Column = 14 Then If Target.Value = "" Then GoTo exitHandler Application.EnableEvents = False Target.Value = Worksheets("Sheet2").Range("B1") _ .Offset(Application.WorksheetFunction _ .Match(Target.Value, Worksheets("Sheet2").Range("DID"), 0), 0) End If exitHandler: Application.EnableEvents = True Exit Sub End Sub I would be happy to send you a copy if it will help. "Dave Peterson" wrote: Nope. Maybe you could share some details. The address of the range that holds the names and the id's. The cell that's getting the data|validation. And the code that you tried. Shawnn wrote: I would use an adjacent cell to make this project much easier on me. Unfortunately I have to follow strict state reporting guidelines. With that being said, I have tried for several hours to duplicate the example in the download you provided but I keep getting errors. The example is exactly what I want to do though. Do you know of a step by step guide? "Dave Peterson" wrote: You could use some code to replace the name with the id number. But I would would use an adjacent cell so that I could display both. If A1 contained the name (a cell with data|validation???), then in B1: =if(a1="","",vlookup(a1,sheet2!a:b,2,false) Where Sheet2 contained the list of names in column A and the ID's in column B. But if you want, Debra Dalgleish has a sample workbook he http://contextures.com/excelfiles.html#DataVal Look for: DV0004 - Data Validation Change -- Select a Product from the Data Validation list; an event procedure changes the product name to a product code. DataValCode.zip 8 kb Shawnn wrote: I have a form that uses a dropdown list to show employee names. What I want to do is allow the names to be selected in the dropdown list but the employee number to actually be inserted into the form. Is this even an option? -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excell Dropdown List. Display alternate text than found in li
I don't think that Data|validation is that customizable.
Shawnn wrote: OK thank you sooo much that worked great! Last issue.. the dropdown list is only as wide as the cell. Is there a way to keep the cell size but force the dropdown list to be as wide as the text inside? "Shawnn" wrote: sorry i did not see that post.... I am working on it now. Thanks Bro. "Dave Peterson" wrote: Only what I suggested before. Shawnn wrote: First, thanks for all the help! Next, I got the multiple cells to work as long as they are using the same list. I am having a problem getting other cells to do it for different lists. Here is my code::: Private Sub Worksheet_Change(ByVal Target As Range) Dim res As Variant If Target.Cells.Count 1 Then Exit Sub End If If Intersect(Target, Me.Range("N7, AE7")) Is Nothing Then Exit Sub End If If Target.Value = "" Then Exit Sub End If Application.EnableEvents = False With Worksheets("Sheet2") res = Application.Match(Target.Value, .Range("Weekday"), 0) If IsError(res) Then MsgBox "Design error!" 'this shouldn't happen Else Target.Value = .Range("Weekday").Offset(0, 1)(res).Value End If End With Application.EnableEvents = True End Sub Private Sub object_Change() Dim res As Variant If Target.Cells.Count 1 Then Exit Sub End If If Intersect(Target, Me.Range("AI9")) Is Nothing Then Exit Sub End If If Target.Value = "" Then Exit Sub End If Application.EnableEvents = False With Worksheets("Sheet2") res = Application.Match(Target.Value, .Range("Security"), 0) If IsError(res) Then MsgBox "Design error!" 'this shouldn't happen Else Target.Value = .Range("Security").Offset(0, 1)(res).Value End If End With Application.EnableEvents = True End Sub As you can see, I want AI9 to read from the list called security, not weekday. I will need to repeat this for another 20 or so cells... any advise? "Dave Peterson" wrote: I'd try changing this line: If Intersect(Target, Me.Range("N8")) Is Nothing Then to something like: If Intersect(Target, Me.Range("N8:N18,L22:m25,x15")) Is Nothing Then Shawnn wrote: Ok I will try that. In the mean time... how do i do this for multiple cells and lists? Lets just say I have 10 different areas i need to use this code. How would I set that up? "Dave Peterson" wrote: First, merged cells cause nothing but grief. I do my best to avoid them. But I merged N8:P8 and this seemed to work ok for me. I also named the range List (not DID). In xl2007, DID will look like a column, so I wouldn't use that. Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim res As Variant If Target.Cells.Count 1 Then Exit Sub End If If Intersect(Target, Me.Range("N8")) Is Nothing Then Exit Sub End If If Target.Value = "" Then Exit Sub End If Application.EnableEvents = False With Worksheets("Sheet2") res = Application.Match(Target.Value, .Range("List"), 0) If IsError(res) Then MsgBox "Design error!" 'this shouldn't happen Else Target.Value = .Range("list").Offset(0, 1)(res).Value End If End With Application.EnableEvents = True End Sub Shawnn wrote: I have several areas that require a code similar to a state abbreviation. For example the word Statutory Burglary mast be recorded as SB. So I have set up multiple colums on Sheet2. A1:A10 are titles, B1:B10 are the abreaviations. The next is C1:C10 are titles and D1:D10 are abbreviations and so on. I used the DV dropdown in my document cell N8 (which is a merged cell) to show the list from A1:A10. Once the ull name is selected I need it to show the abbreviation in the form. I need this for many areas in my report. I used the following code. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Then GoTo exitHandler If Target.Column = 14 Then If Target.Value = "" Then GoTo exitHandler Application.EnableEvents = False Target.Value = Worksheets("Sheet2").Range("B1") _ .Offset(Application.WorksheetFunction _ .Match(Target.Value, Worksheets("Sheet2").Range("DID"), 0), 0) End If exitHandler: Application.EnableEvents = True Exit Sub End Sub I would be happy to send you a copy if it will help. "Dave Peterson" wrote: Nope. Maybe you could share some details. The address of the range that holds the names and the id's. The cell that's getting the data|validation. And the code that you tried. Shawnn wrote: I would use an adjacent cell to make this project much easier on me. Unfortunately I have to follow strict state reporting guidelines. With that being said, I have tried for several hours to duplicate the example in the download you provided but I keep getting errors. The example is exactly what I want to do though. Do you know of a step by step guide? "Dave Peterson" wrote: You could use some code to replace the name with the id number. But I would would use an adjacent cell so that I could display both. If A1 contained the name (a cell with data|validation???), then in B1: =if(a1="","",vlookup(a1,sheet2!a:b,2,false) Where Sheet2 contained the list of names in column A and the ID's in column B. But if you want, Debra Dalgleish has a sample workbook he http://contextures.com/excelfiles.html#DataVal Look for: DV0004 - Data Validation Change -- Select a Product from the Data Validation list; an event procedure changes the product name to a product code. DataValCode.zip 8 kb Shawnn wrote: I have a form that uses a dropdown list to show employee names. What I want to do is allow the names to be selected in the dropdown list but the employee number to actually be inserted into the form. Is this even an option? -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dropdown list doesn't display from the top of the list | Excel Discussion (Misc queries) | |||
Dropdown List - list item endings not visible if column too narrow | Excel Discussion (Misc queries) | |||
How do I show a value for selected text from a dropdown list? | Excel Discussion (Misc queries) | |||
result of selecting from the dropdown list should be a dropdown list | Excel Worksheet Functions | |||
result of selecting from the dropdown list should be a dropdown list | Excel Worksheet Functions |