Unique Data Validation without Unique Formula (2016 Excel)

[Glasgowsmile]

I have this formula in a newer version of Excel report and it works great. It goes through every day in column C (formatted like such: mm/dd/yyyy) and gives me back the MMMM YYYY value which I use in data validation to display each month for a drop-down list.

=UNIQUE(TEXT($C$14:$C$377, "MMMM YYYY"))

Data validation formula:

=OFFSET(DateRanges,0,0,COUNTA(DateRanges),1)

The problem is... I don't know how to create the same data validation list without this unique formula so that it works on older versions of excel.

 

[Peter_SSs]

For the moment, I have assumed that your dates in column C are in ascending order.
If so, see if you could adapt this column E approach (column D is just for comparison using the UNIQUE function)

For the future I suggest that you investigate XL2BB for providing sample data (& expected results) to make it easier for helpers by not having to manually type out sample data to test with.

23 08 17.xlsm
	C	D	E
14	13/03/2019	March 2019	March 2019
15	5/04/2019	April 2019	April 2019
16	8/04/2019	May 2019	May 2019
17	7/05/2019	June 2019	June 2019
18	25/06/2019	July 2019	July 2019
19	20/07/2019	September 2019	September 2019
20	8/09/2019	October 2019	October 2019
21	21/10/2019	November 2019	November 2019
22	22/11/2019	December 2019	December 2019
23	29/11/2019	August 2020	August 2020
24	1/12/2019	September 2020	September 2020
25	9/08/2020	October 2020	October 2020
26	19/08/2020	November 2020	November 2020
27	1/09/2020	February 2021	February 2021
28	8/09/2020	April 2021	April 2021
29	13/09/2020	May 2021	May 2021
30	30/09/2020	June 2021	June 2021
31	7/10/2020	July 2021	July 2021
32	9/10/2020	September 2021	September 2021
33	5/11/2020	October 2021	October 2021
34	19/02/2021	November 2021	November 2021
35	3/04/2021	March 2022	March 2022
36	4/05/2021	April 2022	April 2022
37	6/05/2021	August 2022	August 2022
38	13/06/2021	November 2022	November 2022
39	16/07/2021
40	8/09/2021
41	3/10/2021
42	18/11/2021
43	13/03/2022
44	3/04/2022
45	12/08/2022
46	30/11/2022
Unique dates

Cell Formulas
Range	Formula
D14:D38	D14	=UNIQUE(TEXT($C$14:$C$46, "MMMM YYYY"))
E14:E46	E14	=IFERROR(TEXT(AGGREGATE(15,6,C$14:C$46/ISNA(MATCH(TEXT(C$14:C$46,"MMMM YYYY"),E$13:E13,0)),1),"MMMM YYYY"),"")
Dynamic array formulas.

If the named range DateRanges was E14:E46 in my sample sheet, then your DV formula would change slightly to

=OFFSET(DateRanges,0,0,COUNTIF(DateRanges,"?*"),1)