Creating Dynamic, Unique Arrays Dependent on Multiple Criteria or Subset of Data

by Joe   Last Updated August 14, 2019 08:01 AM

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:

  1. No region can have more than one of the same date, even if the values are different.

To control this, I utilize two formulas:

  1. A Dynamic Named Range that creates a list of unique dates.
  2. A conditional formatting rule that flags entries that have duplicate dates.

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.



Answers 1


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:

=COUNTIFS(INDIRECT("Table[Date]"),INDIRECT("Table[@Date]"),INDIRECT("Table[Region]"),INDIRECT("Table[@Date]"))>1
Joe
Joe
July 17, 2014 15:42 PM

Related Questions


Updated April 09, 2015 03:00 AM

Updated May 26, 2015 01:00 AM

Updated June 29, 2017 19:01 PM

Updated August 07, 2019 12:01 PM