Automatically add new week ending to drop down list as past week ends

[AT_TreeFortConsulting]

I'm creating an activity tracker to use with coaching and consulting clients, and have built a dashboard that offers a look at current and past performance based on a drop down list with week ending dates. I would like to set the drop down list up so that it automatically adds a new week ending as a new week begins in an attempt to keep the drop down list manageable. I'm assuming I will need to use a reference to today's date, and VBA in some way to make this work, if it's possible. Any guidance or input is greatly appreciated.

Adam

 

[Eric W]

Welcome to the MrExcel forum!

You can do it without any VBA. Create a new sheet. Put your first date in A1, put the A2 formula in, and drag down as far as you want. Then go to the Name Manager on the Formulas tab, and enter the Name formula below. Then go to your drop-down cell and use the Name as your formula. Make sure to use the bottom cell in your created range in the Name formula.

Book1
	A	B	C
1	7/2/2021		7/30/2021
2	7/9/2021
3	7/16/2021
4	7/23/2021
5	7/30/2021
6	8/6/2021
7	8/13/2021
8	8/20/2021
9	8/27/2021
10	9/3/2021
11	9/10/2021
Sheet11

Cell Formulas
Range	Formula
A2:A11	A2	=A1+7

Named Ranges
Name	Refers To	Cells
Week_ending_dates	=Sheet11!$A$1:INDEX(Sheet11!$A:$A,MATCH(TODAY(),Sheet11!$A$1:$A$50))	A2

Cells with Data Validation
Cell	Allow	Criteria
C1	List	=Week_ending_dates

 

[AT_TreeFortConsulting]

Welcome to the MrExcel forum!

You can do it without any VBA. Create a new sheet. Put your first date in A1, put the A2 formula in, and drag down as far as you want. Then go to the Name Manager on the Formulas tab, and enter the Name formula below. Then go to your drop-down cell and use the Name as your formula. Make sure to use the bottom cell in your created range in the Name formula.

Book1
	A	B	C
1	7/2/2021		7/30/2021
2	7/9/2021
3	7/16/2021
4	7/23/2021
5	7/30/2021
6	8/6/2021
7	8/13/2021
8	8/20/2021
9	8/27/2021
10	9/3/2021
11	9/10/2021
Sheet11

Cell Formulas
Range	Formula
A2:A11	A2	=A1+7

Named Ranges
Name	Refers To	Cells
Week_ending_dates	=Sheet11!$A$1:INDEX(Sheet11!$A:$A,MATCH(TODAY(),Sheet11!$A$1:$A$50))	A2

Cells with Data Validation
Cell	Allow	Criteria
C1	List	=Week_ending_dates

Hi Eric,

Thanks for your input, but I'm also looking for the ability to select a past week ending date from the drop down to view a past week's performance. Do you know a way to accomplish that?

Adam

 

[Eric W]

That's what my suggestion does. It will create a drop-down list starting with the date in A1, and ending with the most recent week ending date.

 

[AT_TreeFortConsulting]

Hi Eric,

Thanks, I was not referencing my list correctly, but figured that out. Now it works. I can include the current week ending by adding 7 to the TODAY formula, correct? It seems to be working.

Thanks so much for your help.

Adam

 

[Eric W]

Yes, you can add 7 to TODAY() if you want.

Happy to help! ?