How to have Excel data validation display different data in drop down than is actually validated
- by Memitim
How can I provide a user with a drop-down menu in a cell that displays the contents from one column but actually writes the value from a different column to the cell and validates against the values from that second column?
I have a bit of code that very nearly does this (credit: DV0005 from the Contextures site):
Private Sub Worksheet_Change(ByVal Target As range)
On Error GoTo errHandler
If Target.Cells.Count > 1 Then GoTo exitHandler
If Target.Column = 10 Then
If Target.Value = "" Then GoTo exitHandler
Application.EnableEvents = False
Target.Value = Worksheets("Measures").range("B1") _
.Offset(Application.WorksheetFunction _
.Match(Target.Value, Worksheets("Measures").range("Measures"), 0) - 1, 1)
End If
The drop-down displays the values from one column, for example Column B, but when selected actually writes the value on the same row from Column C to the cell. However, data validation is actually validating against Column B, so if I manually enter something from Column C in the cell and try to move to another cell, data validation throws an error.
