in-cell dropdown for vlookup return

[MikeF]

Uncertain if this can be done as described, but giving it a shot here:

Seattle Portland San Francisco Los Angeles
Truck 1 - 1,000 $1,000
Truck 2 - 2,000
Truck 3 - 3,000 $3,000
Truck 4 - 4,000
Truck 5 - 5,000 $5,000

City row text above is in b1:e1 .
Column text is in a2:a6 , fed by field "TruckPrice" from range "Table" on
anther sheet, which separates each truck and its price --- fields are Truck
/ Price / TruckPrice [=Truck&" "&Price].

Data validation for cells c2:e6 is the column text, ie the trucks and their
prices.
There is a table elsewhere that
The objective is simple. Examples as outlined above:
- drop "Truck 1 - 1,000" into b2 and the value in the cell becomes $1,000.
- drop "Truck 3 - 3,000" into d4 and the value in the cell becomes $3,000.
- drop "Truck 5 - 5,000" into c6 and the value in the cell becomes $5,000.
.... And so on for the relevant range.

The solution is apparently not so simple, it dropping down a text field in a
cell to return a number in the same cell.
Have tried numberous combinations of vlookup/offset, etc but to no avail.

Can this be accomplished?

Thanx in advance.

- Mike

 

[Patrick Molloy]

the only way I got something to work was a custom data validation, but that
required the user to enter the correct amount - the validation threows an
error if the entry is incorrect.

That isn't the excercide. what yuo want is to have a blank cell, or have the
drop-down show the correct amount.
So i chose to use the CHANGE event for the sheet. The code checks that the
cell is in column A and that it starts withthe word Truck. Then it updates
the validation rule for each cell in that row with the truck value, and
clears the cell.
Right click the sheet tab, select View Code and paste this code

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count = 1 And Target.column=1 And Target.Value Like "Truck*" Then
Dim cell As Range
Dim text As String
text = Mid(Target.Value, InStr(Target.Value, "-") + 1)
Set cell = Target.Offset(, 1)
Do Until Cells(1, cell.Column) = ""
With cell.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop,
Operator:= _
xlBetween, Formula1:=text
'.Formula1 = "0," & text
End With
cell.Value = ""
Set cell = cell.Offset(, 1)
Loop
End If
End Sub

 

[MikeF]

Patrick,

Thank you for the reply.
Uncertain why, but it doesn't work on my sheet.
Looks like it should, but I just get the full text from Column A.
Also, perhaps I should have specified that "Truck..." is an analogy to each
truck's load, which is all over the map, so "Like Truck*" isn't applicable.

- Mike

the only way I got something to work was a custom data validation, but that
required the user to enter the correct amount - the validation threows an
error if the entry is incorrect.

That isn't the excercide. what yuo want is to have a blank cell, or have the
drop-down show the correct amount.
So i chose to use the CHANGE event for the sheet. The code checks that the
cell is in column A and that it starts withthe word Truck. Then it updates
the validation rule for each cell in that row with the truck value, and
clears the cell.
Right click the sheet tab, select View Code and paste this code

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count = 1 And Target.column=1 And Target.Value Like "Truck*" Then
Dim cell As Range
Dim text As String
text = Mid(Target.Value, InStr(Target.Value, "-") + 1)
Set cell = Target.Offset(, 1)
Do Until Cells(1, cell.Column) = ""
With cell.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop,
Operator:= _
xlBetween, Formula1:=text
'.Formula1 = "0," & text
End With
cell.Value = ""
Set cell = cell.Offset(, 1)
Loop
End If
End Sub

Uncertain if this can be done as described, but giving it a shot here:

Seattle Portland San Francisco Los Angeles
Truck 1 - 1,000 $1,000
Truck 2 - 2,000
Truck 3 - 3,000 $3,000
Truck 4 - 4,000
Truck 5 - 5,000 $5,000

City row text above is in b1:e1 .
Column text is in a2:a6 , fed by field "TruckPrice" from range "Table" on
anther sheet, which separates each truck and its price --- fields are Truck
/ Price / TruckPrice [=Truck&" "&Price].

Data validation for cells c2:e6 is the column text, ie the trucks and their
prices.
There is a table elsewhere that
The objective is simple. Examples as outlined above:
- drop "Truck 1 - 1,000" into b2 and the value in the cell becomes $1,000.
- drop "Truck 3 - 3,000" into d4 and the value in the cell becomes $3,000.
- drop "Truck 5 - 5,000" into c6 and the value in the cell becomes $5,000.
... And so on for the relevant range.

The solution is apparently not so simple, it dropping down a text field in a
cell to return a number in the same cell.
Have tried numberous combinations of vlookup/offset, etc but to no avail.

Can this be accomplished?

Thanx in advance.

- Mike

 

[Patrick Molloy]

I made three changes
(1) amending the IF just to check that the cell in A isn't empty,(2) used
InstrRev to get the last ocurrance of '-' in case rthere are more than one
instance 'bigbus - a - 1000' for example; and (3) check to see if the
resultant text is numeric

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count = 1 And Target.Column = 1 And Target.Value <> "" Then
Dim cell As Range
Dim text As String

text = Mid(Target.Value, InStrRev(Target.Value, "-") + 1)
If IsNumeric(text) Then

Set cell = Target.Offset(, 1)
Do Until Cells(1, cell.Column) = ""
With cell.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop,
Operator:= _
xlBetween, Formula1:=text
End With
cell.Value = ""
Set cell = cell.Offset(, 1)
Loop

End If
End If
End Sub

Patrick,

Thank you for the reply.
Uncertain why, but it doesn't work on my sheet.
Looks like it should, but I just get the full text from Column A.
Also, perhaps I should have specified that "Truck..." is an analogy to each
truck's load, which is all over the map, so "Like Truck*" isn't applicable.

- Mike

the only way I got something to work was a custom data validation, but that
required the user to enter the correct amount - the validation threows an
error if the entry is incorrect.

That isn't the excercide. what yuo want is to have a blank cell, or have the
drop-down show the correct amount.
So i chose to use the CHANGE event for the sheet. The code checks that the
cell is in column A and that it starts withthe word Truck. Then it updates
the validation rule for each cell in that row with the truck value, and
clears the cell.
Right click the sheet tab, select View Code and paste this code

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count = 1 And Target.column=1 And Target.Value Like "Truck*" Then
Dim cell As Range
Dim text As String
text = Mid(Target.Value, InStr(Target.Value, "-") + 1)
Set cell = Target.Offset(, 1)
Do Until Cells(1, cell.Column) = ""
With cell.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop,
Operator:= _
xlBetween, Formula1:=text
'.Formula1 = "0," & text
End With
cell.Value = ""
Set cell = cell.Offset(, 1)
Loop
End If
End Sub

Uncertain if this can be done as described, but giving it a shot here:

Seattle Portland San Francisco Los Angeles
Truck 1 - 1,000 $1,000
Truck 2 - 2,000
Truck 3 - 3,000 $3,000
Truck 4 - 4,000
Truck 5 - 5,000 $5,000

City row text above is in b1:e1 .
Column text is in a2:a6 , fed by field "TruckPrice" from range "Table" on
anther sheet, which separates each truck and its price --- fields are Truck
/ Price / TruckPrice [=Truck&" "&Price].

Data validation for cells c2:e6 is the column text, ie the trucks and their
prices.
There is a table elsewhere that
The objective is simple. Examples as outlined above:
- drop "Truck 1 - 1,000" into b2 and the value in the cell becomes $1,000.
- drop "Truck 3 - 3,000" into d4 and the value in the cell becomes $3,000.
- drop "Truck 5 - 5,000" into c6 and the value in the cell becomes $5,000.
... And so on for the relevant range.

The solution is apparently not so simple, it dropping down a text field in a
cell to return a number in the same cell.
Have tried numberous combinations of vlookup/offset, etc but to no avail.

Can this be accomplished?

Thanx in advance.

- Mike

 

[MikeF]

Patrick,
Thanx again.

But ... it's not working.
Tried it with the truck example, copied the code properly into the worksheet
"module", but when I drop-down it's just the vlookup return.

Does it work properly for you??
- Mike

I made three changes
(1) amending the IF just to check that the cell in A isn't empty,(2) used
InstrRev to get the last ocurrance of '-' in case rthere are more than one
instance 'bigbus - a - 1000' for example; and (3) check to see if the
resultant text is numeric

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count = 1 And Target.Column = 1 And Target.Value <> "" Then
Dim cell As Range
Dim text As String

text = Mid(Target.Value, InStrRev(Target.Value, "-") + 1)
If IsNumeric(text) Then

Set cell = Target.Offset(, 1)
Do Until Cells(1, cell.Column) = ""
With cell.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop,
Operator:= _
xlBetween, Formula1:=text
End With
cell.Value = ""
Set cell = cell.Offset(, 1)
Loop

End If
End If
End Sub

Patrick,

Thank you for the reply.
Uncertain why, but it doesn't work on my sheet.
Looks like it should, but I just get the full text from Column A.
Also, perhaps I should have specified that "Truck..." is an analogy to each
truck's load, which is all over the map, so "Like Truck*" isn't applicable.

- Mike

the only way I got something to work was a custom data validation, but that
required the user to enter the correct amount - the validation threows an
error if the entry is incorrect.

That isn't the excercide. what yuo want is to have a blank cell, or have the
drop-down show the correct amount.
So i chose to use the CHANGE event for the sheet. The code checks that the
cell is in column A and that it starts withthe word Truck. Then it updates
the validation rule for each cell in that row with the truck value, and
clears the cell.
Right click the sheet tab, select View Code and paste this code

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count = 1 And Target.column=1 And Target.Value Like "Truck*" Then
Dim cell As Range
Dim text As String
text = Mid(Target.Value, InStr(Target.Value, "-") + 1)
Set cell = Target.Offset(, 1)
Do Until Cells(1, cell.Column) = ""
With cell.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop,
Operator:= _
xlBetween, Formula1:=text
'.Formula1 = "0," & text
End With
cell.Value = ""
Set cell = cell.Offset(, 1)
Loop
End If
End Sub



:

Uncertain if this can be done as described, but giving it a shot here:

Seattle Portland San Francisco Los Angeles
Truck 1 - 1,000 $1,000
Truck 2 - 2,000
Truck 3 - 3,000 $3,000
Truck 4 - 4,000
Truck 5 - 5,000 $5,000

City row text above is in b1:e1 .
Column text is in a2:a6 , fed by field "TruckPrice" from range "Table" on
anther sheet, which separates each truck and its price --- fields are Truck
/ Price / TruckPrice [=Truck&" "&Price].

Data validation for cells c2:e6 is the column text, ie the trucks and their
prices.
There is a table elsewhere that
The objective is simple. Examples as outlined above:
- drop "Truck 1 - 1,000" into b2 and the value in the cell becomes $1,000.
- drop "Truck 3 - 3,000" into d4 and the value in the cell becomes $3,000.
- drop "Truck 5 - 5,000" into c6 and the value in the cell becomes $5,000.
... And so on for the relevant range.

The solution is apparently not so simple, it dropping down a text field in a
cell to return a number in the same cell.
Have tried numberous combinations of vlookup/offset, etc but to no avail.

Can this be accomplished?

Thanx in advance.

- Mike