Automatically select a selection from a drop down list based on current date

Hi,

I have created a dashboard with donut charts which keep track of a number of tasks completed and the number of tasks still left to complete.

Every week I have to give an update of the progress of work showing the percentage difference and current completed for each task from week to week.

I have created a drop down list giving me a percentage difference for each/new weeks coming using the =Today().

Every week I would like the percentage difference cells B5,C5 and D5 to change automatically, from a specific date in the drop down list (L11) without having to manually select a date from the drop down list. The sheet does it automatically when I open the sheet.

Is this possible?

Welcome to the forum.

You already have a drop-down and it seems to be working - what about it is not working as you want it to? Please show us (manually mocked up) what you want.

You cannot have a formula and data validation list in the same cell - if you do, the formula will be lost the minute anyone uses the DV drop-down. This will require VBA - shall I move the thread for you?

By the way, you need to change P12 to this:

=UNIQUE(FILTER(Percentdata[DATE:],Percentdata[DATE:]<>""))

If you want Excel to find the correct date INSTEAD of having the DV list, then you could use this:

=LET(u,UNIQUE(FILTER(Percentdata[DATE:],Percentdata[DATE:]<>"")),XLOOKUP(TODAY(),u,u,,-1,2))

Do I take it that you want to have both the formula and the drop-down? I am moving this to the VBA section. I suggest you update the title of your first post in this thread to include something about needing both DV drop-down and a formula in the same cell. Good luck!

This seems to be different to what you asked previously - please update the thread title to make this request crystal clear.

Thanks for updating the title, but it doesn't say anything about automating charts - you need to add that so that people know what the whole issue is.

I am wondering now if it is the drop-down or the charts you need help with, or both.

Yes - so we can forget about the charts. Thanks for clarifying.