Validation:List

[James]

I have a list that consists of a number with a description, for example:
123 - Apples
456 - Oranges

what I'd like to do is have that display in my drop down, but once selected,
only display the number?
so in cell A1 I have my dropdown menu, I will see my options as above,
choose "123 - Apples", but only have "123" display in A1.
any ideas?

 

[Bob Phillips]

Assuming your dropdown is data validation, and in F1, try

=LEFT(F1,FIND(" -",F1))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[JE McGimpsey]

You can't have a function and make an entry via validation in the same
cell.

You could use an event macro to modify the entry, then reestablish the
validation list. To be sure it works for XL97 and later, use the
Worksheet_Calculate() macro. If your cell(s) of entry are named "rng",
and your validation list is named "mylist", then put this in your
worksheet code module (right-click the worksheet tab and choose View
Code):

Private Sub Worksheet_Calculate()
Dim rCell As Range
With Application
.EnableEvents = False
.ScreenUpdating = False
End With
With Range("rng")
.Validation.Delete
For Each rCell In .Cells
With rCell
If Len(.Text) > 3 Then .Value = Left(.Text, 3)
End With
Next rCell
With .Validation
.Add Type:=xlValidateList, Formula1:="=mylist"
.IgnoreBlank = True
.InCellDropdown = True
End With
End With
Application.EnableEvents = True
End Sub


You'll also need to enter a formula in the workbook that references the
target range, to ensure the Calculation event is fired when an item from
the validation list is changed, e.g.,

=COUNTA(rng)

Note that the formula could be on a different, even hidden, worksheet.

Also note that this does not prevent the user from pasting a non-valid
value into the rng. The code could be modified to handle that situation
if necessary.