change drop down value on the certain date

OshaK

Hello, is it possible to automatically update a drop-down menu values on a certain day of the year?

for example, we have the following values in a drop-down menu. Each year on June 30, our Chief Residents graduate and each of the new years move up and 5 new incoming interns become PGY1.

so I need all Chiefs to switch to Residents Alumni, PGY4 01-06 to Chiefs 1-6 and so on. Also, some years will have an uneven number - in some there 6 or 4 but mostly have 5.

We're currently doing it manually.

thank you,

• Chief Resident 1
• Chief Resident 2
• Chief Resident 3
• Chief Resident 4
• Chief Resident 5
• Chief Resident 6
• PGY4 01
• PGY4 02
• PGY4 03
• PGY4 04
• PGY4 05
• PGY4 06
• PGY3 01
• PGY3 02
• PGY3 03
• PGY3 04
• PGY3 05
• PGY3 06
• PGY2 01
• PGY2 02
• PGY2 03
• PGY2 04
• PGY2 05
• PGY2 06
• PGY1 01
• PGY1 02
• PGY1 03
• PGY1 04
• PGY1 05
• PGY1 06
• Research Y2 01
• Research Y2 02
• Research Y2 03
• Research Y1 01
• Research Y1 02
• Research Y1 03
• Resident Alumni

Paul Newcome

You could enter the date when they receive the first title and then use a formula to automatically update the titles based on that date.

From there you could use Data Shuttle or the API to update the dropdown list.

OshaK

thank you, @Paul Newcome what would be the formula?

I'm just learning SS, but I don't know how to write them, sorry. Thank you.

Paul Newcome

EDIT: Accidentally clicked the post button before finishing the formula.

I would first create a table similar to the one in your original post that has all titles listed, but have the first title at the top of the list and the last title at the bottom (so basically reverse order from the original post).

In the working sheet, you would have a column that indicates which year they got the first title. Then the title column would have something along the lines of...

=INDEX({List Sheet List Column}, YEAR(TODAY()) - [Year Column name]@row - IF(TODAY()< DATE(YEAR(TODAY()), 6, 30), 0, -1))

OshaK

thank you @Paul Newcome I need a full date because the go by the academic not calendar year. It starts June 25 and ends June 24 every calendar year. Is it possible to add the full date when the switch happens? thank you

Paul Newcome

The above is set to switch every year on June 30th. If you needed a different date for the switch, then you would update the 6 and the 30 as needed. Or am I misunderstanding what you need? Are you able to provide some screenshots with sample data for context?