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

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.

--
Regards,
Tom Ogilvy


"twirth" wrote in message
...
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

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!


"Tom Ogilvy" wrote:

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.

--
Regards,
Tom Ogilvy


"twirth" wrote in message
...
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

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

"twirth" wrote:

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!


"Tom Ogilvy" wrote:

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.

--
Regards,
Tom Ogilvy


"twirth" wrote in message
...
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

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

--
Regards,
Tom Ogilvy


"twirth" wrote in message
...
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

"twirth" wrote:

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!


"Tom Ogilvy" wrote:

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.

--
Regards,
Tom Ogilvy


"twirth" wrote in message
...
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

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

"Tom Ogilvy" wrote:

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

--
Regards,
Tom Ogilvy


"twirth" wrote in message
...
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

"twirth" wrote:

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!


"Tom Ogilvy" wrote:

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.

--
Regards,
Tom Ogilvy


"twirth" wrote in message
...
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,

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" wrote:

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

--
Regards,
Tom Ogilvy


"twirth" wrote in message
...
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

"twirth" wrote:

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!


"Tom Ogilvy" wrote:

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.

--
Regards,
Tom Ogilvy


"twirth" wrote in message
...
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