Drop down list question

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
5,699
Office Version
  1. 2007
Platform
  1. Windows
I enter my mileage onto a spreadsheet for various garages.
I wish to have a drop down list with the garages name & miles to & from there.

In the drop down list i would need to see the name of the garage & also the miles BUT when i make my selection i would only require the mileage to be entered into the cell.


The worksheet is called MILEAGE & the drop down list will be placed into cells D3:D30

Is this possible if so please advise.

Many thanks.
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
Formula.

Use Data Validation in Cell A1 to be you list in D3:D30

This should return your garage and mileage.

then in a2 enter
=RIGHT(A1,LEN(A1)-SEARCH("-",A1,1))


in the SEARCH("-" use whatever character you are separating you garage an mileage with.


Example

Phoenix-134 you would use SEARCH("-"

Phoenix:134 then use SEARCH(":"

Phoenix,134 then use SEARCH(","


hth,

Ross
 
Upvote 0
Hi,
Thanks for that but not what i was looking for.

In my drop down list say there are 10 garages but i select Regal Garage 26
When i this garage i need just the 26 to then be entered into this cell.
 
Upvote 0
you have a space in the city name:

change the city name to
Regal Garage-26

then use this:
=RIGHT(A1,LEN(A1)-SEARCH("-",A1,1))
 
Upvote 0
Hi,

I think it needs to be VBA or maybe an IF THEN or IF ELSE statement.

Something like if active cell is Regal then enter 26 into active cell, then add the other garages into the code.
Range would be D3:D30
 
Upvote 0
Something like below but correctly written.

Sub mileage()
Dim cell As Range
For Each cell In Range("D3:D30")
If ActiveCell.Value = "banwell" Then
ActiveCell = 1
ElseIf ActiveCell.Value = "locking" Then
ActiveCell = 4
ElseIf ActiveCellell.Value = "winscombe" Then
ActiveCell = 5
ElseIf ActiveCell.Value = "hutton" Then
ActiveCell = 6
ElseIf ActiveCell.Value = "churchill" Then
ActiveCell = 7
End If
Next cell
End Sub
 
Upvote 0
Hi,

The below code works fine but can you advise an edit so it also works for the range D3:D30
Im stuck with just the cell D3 at present.

Thanks

Code:
Private Sub Worksheet_Change(ByVal Target As Range)If Range("D3").Value = "B" Then
Range("D3").Value = "1"
ElseIf Range("D3").Value = "L" Then
Range("D3").Value = "4"
ElseIf Range("D3").Value = "W" Then
Range("D3").Value = "5"
ElseIf Range("D3").Value = "H" Then
Range("D3").Value = "6"
ElseIf Range("D3").Value = "C" Then
Range("D3").Value = "7"
End If
End Sub
 
Last edited:
Upvote 0
try this,

Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Selection.Count = 1 Then
If Not Intersect(Target, Range("D3:D30")) Is Nothing Then


If Target.Value = "B" Then Target.Value = "1"
If Target.Value = "L" Then Target.Value = "4"
If Target.Value = "W" Then Target.Value = "5"
If Target.Value = "H" Then Target.Value = "6"
If Target.Value = "C" Then Target.Value = "7"


End If
End If

End Sub

Ross
 
Upvote 0

Forum statistics

Threads
1,223,277
Messages
6,171,148
Members
452,382
Latest member
RonChand

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top