multiple validations

  • Thread starter Thread starter Koby W.
  • Start date Start date
K

Koby W.

I have an excel file that has validation set up to choose numbers from a
list. In the column next to the numbers, I have a description of what the
numbers represent. Is there anyway to be view both columns in my drop down
list, but only insert the numbers? I thought maybe I could just merge the
two columns together and limit the number of characters that were actually
inserted into the cell, but I couldn't figure out how to do that. Any help
would be appreciated.
 
Koby,

Make up your list of validation values like

1 Description
2 Other stuff
3 blah blah
.....

So that the number is the left part, and the description is the right part of the string, separated
by a space.

Then copy this code, right-click the sheet tab, select "View Code" and paste the code into the
window that appears. Change the H5:H10 to the address of the range that you want to have this work
on.

Then, when the call value is changed, only the number to the left will appear in the cell.

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("H5:H10")) Is Nothing Then Exit Sub
If Target.Value = "" Then Exit Sub
Application.EnableEvents = False
On Error Resume Next
Target.Value = Left(Target.Value, InStr(1, Target.Value, " ") - 1)
Application.EnableEvents = True

End Sub

HTH,
Bernie
MS Excel MVP
 
Back
Top