CODEWITHSUNDEEP
vbaexcelvalidation
Greedo
Greedo

Reputation: 5523

Link 2 in-cell data validation lists

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:

  1. Since each formula refers to the other cell, I would get a circular reference
  2. 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.

Upvotes: 1

Views: 444

Answers (1)

Greedo
Greedo

Reputation: 5523

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

Upvotes: 2

Related Questions