Year Dropdown (values change in future)

[duteberta]

Is there a way to create a dropdown of YEAR values whose MIN is any given year (say 2013) and whose MAX value dynamically changes to the Current year + 1?

So let's say that today is in 2024 so the dropdown would show values descending from 2025-2013. Then next year those same dropdown values would AUTOMATICALLY change to 2026-2013. I want this to happen without adjusting the code every year. Hopefully no VBA on this- but if need be then ok.

 

[Fluff]

You can create a list like

=LET(y,YEAR(TODAY())+1,m,2013,SEQUENCE(y-m+1,,y,-1))

and then refer to the cell in datavalidation.

Fluff.xlsm
	A	B	C
1	Years
2	2025		2024
3	2024
4	2023
5	2022
6	2021
7	2020
8	2019
9	2018
10	2017
11	2016
12	2015
13	2014
14	2013
15
Sheet5

Cell Formulas
Range	Formula
A2:A14	A2	=LET(y,YEAR(TODAY())+1,m,2013,SEQUENCE(y-m+1,,y,-1))
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
C2	List	=$A$2#

 

[duteberta]

Thank you very much!

 

[Fluff]

You're welcome.