I'm wondering if what I want to do is possible in Excel:

I have a dashboard report of charts and tables on a worksheet that
summarizes weekly data, which is housed on other worksheets in the same
workbook. This dashboard updates every week as new data is input into the
other worksheets (I've dynamically set this up - defined names - etc.)
However, each weekly dashboard is not saved as new data is input. If
possible, I want to create a drop down menu where you can select the week,
and the dashboard automatically displays the charts and tables with that
week's data. Is this possible?

Also: I want the drop down list to update dynamically with each new week
that occurs - adding one more week option to the end. Can anyone help me out?

Thanks in advance!!