Drop down list question

[ipbr21054]

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.

 

[rpaulson]

are you looking for VBA or Formula solution?

 

[ipbr21054]

Can you advise both and let me choose ?

Thanks

 

[rpaulson]

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

 

[ipbr21054]

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.

 

[rpaulson]

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))

 

[ipbr21054]

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

 

[ipbr21054]

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

 

[ipbr21054]

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

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

 

[rpaulson]

try this,

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