Forum

Unique Data and Dyn...
 
Notifications
Clear all

Unique Data and Dynamic Data Validation

2 Posts
2 Users
0 Reactions
119 Views
(@swallack202)
Posts: 77
Estimable Member
Topic starter
 

Hello -

I've tried a variety of ways to develop dynamic data validation, but can't seem to get it to work. I tried INDIRECT and INDEX/MATCH. I'm also struggling to validate that a pair of values is unique in a range.

The attached XLS has three sheets:

  1. ARTS and TEAMS
    • User creates a list of ARTS, and indicates whether or not each is active 
    • The user creates a list of teams within each ART, and indicates whether or not each is active
    • I use VBA in the Worksheet_Change event to check that the combination of ART + Team is unique. This is not working. It warns that a unique combination is not unique.
  2. Droplists
    • These two pivot tables are used for data validation
    • They show unique, sorted, and active ARTs and Teams, respectively
  3. Data Match
    • First, the user selects an ART (via data validation list)
    • Next, the user selects a Team (also via data validation list)
    • I need the second list (Team) to depend on the choice in the first list (ART) base on the combinations on the first sheet. This is not working.

Many thanks

 
Posted : 07/06/2022 2:01 pm
(@catalinb)
Posts: 1937
Member Admin
 

Hi Shawn,

Your formula is NOT checking the combination of ART and TEAM columns.

WorksheetFunction.CountIf(EvalRange, Target.Value) just checks the Target.Value , which is Yankees, not ART-1 & Yankees, and that value exists in the EvalRange.

If you want to use a formula, add a helper column ART&Team with the formula: =[@ART]&[@TEAM]

You will be able to compare the current entry against this combined column.

Dim Comb As String
Comb = Me.Cells(Target.Row, Me.ListObjects("TABLE_TEAMS").ListColumns("ART").Range.Column).Value & _
Me.Cells(Target.Row, Me.ListObjects("TABLE_TEAMS").ListColumns("TEAM").Range.Column).Value
If WorksheetFunction.CountIf(Me.ListObjects("TABLE_TEAMS").ListColumns("ART&TEAM").Range, Comb) > 1 Then

...

 
Posted : 16/06/2022 10:34 am
Share: