Auto Sort Range based on current date

[andrewb90]

Hello all,

I have three named ranges on my sheet. (DAYS, MONTHS, & YEARS) They provide values for my combo box userform. What I am trying to do is to have the ranges resorted to match tomorrows date automatically, and I'm not sure how best to accomplish this.
For the MONTH range, since it's April, that would be listed first, then MAY, etc with March at the bottom.
For the DAY as it is the 18th, the 19th would be at the top, then the 20th etc, wrapping around to the 18th being the 31st item.
The Years would be the same way.

Any ideas on how do pull this off?

 

[Yongle]

Try these 3 formula and use as RefersTo in your named range
If starting formula placed in any row other than row 2, adjust accordingly

in A2 copied down (7 cells - one for each day)
=TEXT(TODAY()+ROW()-2,"DDD")

In B2 copied down (12 cells - one for each month)
=TEXT(EDATE(TODAY(),ROW()-2),"MMM")

In C2 copied down (as many cells as you want
=VALUE(YEAR(TODAY()))+ROW()-2


Named Range Refers to:
DAYS =SheetName!$A$2:$A$8
MONTHS =SheetName!$B$2:$B$13
YEARS =SheetName!$C$2:$C$21 (20 years ahead)

 

[andrewb90]

That's brilliant! Works great! Thank you!

 

[andrewb90]

I just discovered one small issue, If the current month is a 30 day month, then 31 will disappear from the list of days. But as these lists fill in a drop down box in my user form, I need to still be able to select 31 should the date selected be a future date. Any ideas on how to accomplish this?

(I'm using"DD" to display day of month as opposed to day of week)

 

[Yongle]

For the DAY as it is the 18th, the 19th would be at the top, then the 20th etc, wrapping around to the 18th being the 31st item.
AND
I need to still be able to select 31 should the date selected be a future date

2 consecutive months with 31 days required to make this work - December and January fit the bill
The range for the dropdown in combobox is A2:A32

formula in A1
=(DATE(2018,12,VALUE(TEXT(TODAY(),"dd"))))
converts today's day to the same day of the month in December

formula in A2 copied down as far as A32
=VALUE(TEXT(A1+1,"dd"))
returns all numbers from 1 to 31

insert check within the userform code to ensure combo date is a valid date