Dynamic Date Drop-down List

[Rob]

I'm trying to create a Validation List that shows a drop-down with three
years worth of dates before and after today's date.

Currently I am using a 2100 row list of manually inputted dates in a
separate worksheet but that just gets cumbersome when I have to constantly
update the date range named "Calendar Dates"

Does anyone know if there is there some sort of Function or code that can do
this dynamically without using 2100 rows?

I'm thinking that sheet3 cell A3 has today's date, A2 has a calculated
back-off date that goes backwards to 365*3 and then the same for A4 but it
goes forwards.

Just Hoping.

Thanks In Advance,
Rob

 

[T. Valko]

Maybe this...

A2:

=EDATE(NOW(),-36)

A3:

=EDATE(NOW(),36)

As the source for the drop down:

=INDEX(range,MATCH(A2,range,0)):INDEX(range,MATCH(A3,range,0))

Note that the EDATE function requires the Analysis ToolPak add-in be
installed if you're using a version of Excel prior to Excel 2007. If you
enter the formula and get a #NAME? error look in Excel help for the EDATE
function. It'll tell you how to fix the problem.

 

[Rob]

Wow, I just learned something new with the EDATE() Function!! Thanks!
Unfortunately the source for the drop down does not seem to work. I think
it's because of the term "range" in it. What am I needing to provide for
that one?

 

[T. Valko]

range = the range of cells where your dates are located.

Let's assume the dates are in the range B1:B2000. Then:

=INDEX(B1:B2000,MATCH(A2,B1:B2000,0)):INDEX(B1:B2000,MATCH(A3,B1:B2000,0))

If the dates are located on a different sheet than where the drop down is
located you'll have to give the formula defined name and then use that
defined name as the source for the drop down.