Link 2 in-cell data validation lists

Question

I have 2 in-cell data validation lists in Excel. Both cells contain dropdown arrows pointing to 2 columns of a table. What I would like is for when 1 cell contains a value, the other cell contains the value from the corresponding row in the table, but its own column, and vice-versa. So if I select a value from the dropdown in cell1 it overwrites cell2, if I select a value from the dropdown in cell2 it overwrites cell1

i.e. for a table GoalTbl with columns cl and d; a cell named cl_val contains a data validation list pointing to GoalTbl[cl]. Another cell called d_val points to GoalTbl[d]

And so to get a d_val based on cl_val I use a formula like =INDEX(GoalTbl[d],MATCH(cl_val,GoalTbl[cl],0))

Similarly to get a value for cl_val based on d_val, =INDEX(GoalTbl[cl],MATCH(d_val,GoalTbl[d],0))

I can't put these formulae in their respective cells for 2 reasons:

Since each formula refers to the other cell, I would get a circular reference
If I use the drop-down arrow to select a value, it overwrites the formula in that cell

So can I get this linked functionality by changing the lists that the data validation points to - or with a VBA approach? I suppose this is a combination of a dynamic default formula for a validation, and an overwriting mechanism for 1 cell based on the other - 2 areas I'm not sure how to tackle simultaneously with formulae.

I don't think you can do this with formula and validation, but you can definitely do this with VBA. Use the Worksheet_Change event, trigger it when a user changes either of the two cells, and fill the other with either a fomula or the value itself. You might want to turn off the error alert, this way allowing values other than the ones on the dropdown to be entered - e.g. the formula. But then again, this would allow your users to simply put anything in those cells. Interesting problem. — vacip

Greedo Greedo · Accepted Answer · 2017-04-03T10:14:08

Thanks for the pointer towards Worksheet_Change, I have a VBA approach;

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, [cl_val]) Is Nothing Then
    With Application.WorksheetFunction
    UI False
    [d_val] = .Index([Goaltbl[d]], .Match([cl_val], [Goaltbl[cl]], 0))
    UI True
    End With
ElseIf Not Intersect(Target, [d_val]) Is Nothing Then
    With Application.WorksheetFunction
    UI False
    [cl_val] = .Index([Goaltbl[cl]], .Match([d_val], [Goaltbl[d]], 0))
    UI True
    End With
End If
End Sub

Where UI is simply a Sub* to turn on/off screen updating and events (I have a Worksheet_Calculate macro elsewhere which I don't want triggered)

Still, a function approach would be nice to know about - I'm sure something can be done by changing the list input

*UI code for reference

Public Sub UI(t As Boolean)
    Application.EnableEvents = t
    Application.ScreenUpdating = t
End Sub

Link 2 in-cell data validation lists

1 Answers