Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
in-cell dropdown for vlookup return
Uncertain if this can be done as described, but giving it a shot he
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
in-cell dropdown for vlookup return
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" wrote: Uncertain if this can be done as described, but giving it a shot he 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
in-cell dropdown for vlookup return
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 "Patrick Molloy" wrote: 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" wrote: Uncertain if this can be done as described, but giving it a shot he 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
in-cell dropdown for vlookup return
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 "MikeF" wrote: 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 "Patrick Molloy" wrote: 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" wrote: Uncertain if this can be done as described, but giving it a shot he 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
in-cell dropdown for vlookup return
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 "Patrick Molloy" wrote: 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 "MikeF" wrote: 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 "Patrick Molloy" wrote: 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" wrote: Uncertain if this can be done as described, but giving it a shot he 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
return value from a dropdown list in other cell | Excel Discussion (Misc queries) | |||
select From dropdown and return another dropdown menu | Excel Discussion (Misc queries) | |||
VLookUp and DropDown List in the same cell | Excel Worksheet Functions | |||
Vlookup to Return Cell Name Not Value? | Excel Programming | |||
VLOOKUP or dropdown in the cell depending on selection in another | Excel Discussion (Misc queries) |