Log In
Register
Home
December 7, 2021
Offline
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:
- 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.
- Droplists
- These two pivot tables are used for data validation
- They show unique, sorted, and active ARTs and Teams, respectively
- 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
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
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
...
1 Guest(s)
