Excell Dropdown List. Display alternate text than found in list.

S

Shawnn

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?
 
D

Dave Peterson

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 here:
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
 
S

Shawnn

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?
 
D

Dave Peterson

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.
 
S

Shawnn

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.
 
D

Dave Peterson

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



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.
Click to expand...
 
S

Shawnn

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?
 
D

Dave Peterson

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
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?
Click to expand...
 
S

Shawnn

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?
 
D

Dave Peterson

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

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?
Click to expand...
 
S

Shawnn

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?
 
D

Dave Peterson

Only what I suggested before.
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?
Click to expand...
 
S

Shawnn

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 said:
sorry i did not see that post.... I am working on it now. Thanks Bro.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Dropdown List 10
Displaying arrow in a dropdown list within cell 2
dropdown list determined by another dropdown list 5
data validation, totaling values based off dropdown lists 5
Help...Using dropdown list 4
How can I use conditional formating in dropdown using named list? 1
Excel viewer can't use dropdown menus?? 2
Validation Dropdown list starts at b ottom of list 2

Top