Dropdown list to return different value

[twirth]

Hello,
Does anyone know how I can make a dropdown list in Excel return a value that
is not necessarily in the list?
Example - the list would include city names and possibly the corresponding
code, but I would like it to return only the corresponding three digit "code".
Langdon City = 292
Munich City = 396
Hannah City = 442
I am assuming this is not possible just using the Data Validation feature in
Excel, and I am more than willing to try it via VB, but I'm not familiar
enough with the code to know where to start!
Any help would be greatly appreciated!
Tanya

 

[Tom Ogilvy]

You could do it with data validation and the change worksheet event.

Assume your list is on sheet2, with the Data in A2:B100

your dropdown is on Sheet1 in cell B9

Create a named range

Insert=>Name=>Define

Name: Data1
Refersto: =OFFSET(Sheet2!$A$2,0,0,COUNTA(Sheet2!$A:$A)-1,1)

Select Sheet1:
Now go to B9 and do Data=>Validation
select the list option
put in =Data1
(the name we created).

Now right click on the sheet tab and select view code.

Paste in this code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng as Range, res as variant
On Error GoTo ErrHandler
If Target.Address = "$B$9" Then
Set rng = Worksheets("Sheet2").Range("Data1")
res = Application.Match(Target, rng, 0)
If Not IsError(res) Then
Application.EnableEvents = True
Target.Value = rng(res, 2)
End If
End If
ErrHandler:
Application.EnableEvents = True
End Sub

go back to sheet1 and select an item from the dropdown in B9.

 

[twirth]

Okay,

Is it possible to do the change worksheet event without creating a named
range on a separate sheet? At the moment, I just have the list entered under
the Data/Validation "Source" box in the Excel menu. I have to submit this
workbook when the data is entered and would prefer not to have any extra
worksheets.

Also, I couldn't see where in the Worksheet Change event code the
corresponding city "codes" are referenced. I would assume that somewhere I
would have to tell VB that if "Langdon City" is chosen from the dropdown box,
excel should enter "292" in its place in that cell.

Thanks for your help!

 

[twirth]

Also, I need to have this type of dropbox entered in more than one cell in
the column- I need to have it available to all cells in column G.

Thanks again!
Tanya

 

[Tom Ogilvy]

the cities were listed in column A of sheet2 and the corresponding code in
column B of sheet2

You can try this adjustement which requires that you enter the data in the
procedure. Sample data has been pre-entered. follow that pattern.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng as Range, v1, v2, idex as long, i as Long

v1 = Array("Langdon City", _
"Munich City", _
"Hannah City")
v2 = Array(292,396,442)
On Error GoTo ErrHandler
if Target.count > 1 then exit sub
idex = -1
If Target.column = 7 Then
for i = lbound(v1) to Ubound(v1)
if lcase(v1(i)) = lcase(Target.value) then
idex = i
exit for
end if
Next
if idex <> -1 then
Application.EnableEvents = True
Target.Value = v2(idex)
End If
End If
ErrHandler:
Application.EnableEvents = True
End Sub

 

[twirth]

That works! You rock - thank you so much for the help!!!
Tanya

 

[twirth]

Tom,

Is it possible to run the worksheet_change event more than once? Because I
need different list info in another column with dropdowns, I attempted to
copy the code directly below itself and change the column and list
information, but I get the error "ambiguous name detected", I'm assuming
because these events are titled the same?

 

[Tom Ogilvy]

If the lists are completely different, then you can just put all the choices
and corresponding results in the two existing arrays. Change the If
statement to

If Target.column = 7 or Target.column = 11 Then ' as an example.

If the choices overlap (both of the lists select cities, but you want to
return something else), then You can put in

Select Case Target.column
Case 7
' code and arrays for column 7
case 11
' code and arrays for column 11

end Select

So all you code has to go in the single change event, but you structure your
code to react accordingly.

 

[twirth]

Thank you - I'm starting to wish I had paid more attention in VB class! I
guess at the time it didn't seem that useful, I mean when would you REALLY
need to make christmas tree bulbs flash different colors?!

One more question and hopefully I'll have this spreadsheet ready to go - one
of my columns has like 60 options for the drop down list - and when I get so
far down the array I get a message that I have too many lines in my array.
Is there any way to add more lines and keep going with the list?

Tanya

 

[Tom Ogilvy]

If you have 60 options, I have a hard time believing they are not listed on
a sheet someplace. But whatever.

You don't have to do

v1 = Array("Langdon City", _
"Munich City", _
"Hannah City")


That was just to make it look nice. I am guessing you are are getting too
many line continuation characters. You can put a massive amount of choices
on each line

v1 = Array("City1","City2","City3","City4","City5","City6","City7", _
"City8","City9","City10","City11","City12", . . .,"City250")

as an example.