I have a Table structured in this manner:
City Region Date Value ABC 123 Jan1 10% DEF 999 Feb1 7%
The table is fed by user-entry, but there is an important rule:
To control this, I utilize two formulas:
I am having trouble with #1. #2 I have tested to work fine.
My attempt was to create this named range, simply called UniqueDates.
=OFFSET( OFFSET(Table!$C$9,0,0,1,1), MATCH(0, COUNTIF(Table[@Region],Entry[Region]),0) -1,0, SUM(COUNTIFS(Table[Region],Entry[@Region])) )
I have experimented with trying different criteria combinations, but cannot seem to create a list of unique dates that have been entered thus far. Instead, I get either the wrong dates, or non-distinct results.
It is important to note that the result of this formula is not stored in Data Validation. I take it directly to the conditional formatting rule.
I over-complicated the problem. The solution was simply to drop named ranges altogether and do the duplication check within the Conditional Formatting rule itself: