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.
Best Answer
The only way I see would be to remove the data validation, and code your own drop down box.
The advantage of this is that the drop down will hide the actual cell, so the cell itself can still be edited as normal.
This code (from here) will add a drop down, and when an item is selected, put that value in the cell, change another cell based on the item selected, and then remove itself so no drop down exists. You should be able to use this code for your own fun.
Code reproduced here, just in case the link breaks: