Rotating List with a Twist

[Code_]

I need to make a rotating list of available doctors that are selectable in a drop down in a cell. These doctors are assigned patients in ABC order.

So our doctors are Adoc, Bdoc, Cdoc, Ddoc.

Patient comes in, the drop down shows that they should go to Adoc (shown at top of list, followed by Bdoc, Cdoc, etc).
Patient comes in, the drop down shows that they should go to Bdoc. (shown at top of list, followed by Cdoc, Ddoc, etc).
Patient comes in, the drop down shows that they should go to Cdoc. (shown at top of list, followed by Ddoc, Adoc, Bdoc, etc).
Patient comes in, the drop down shows that they should go to Ddoc.
Patient comes in, the drop down shows that they should go to Adoc.
Patient comes in, the drop down shows that they should go to Bdoc.
.
.
etc.

The twist: If Bdoc is off (vacation), they don't count in the rotation. So the previous scenario goes:

Patient comes in, the drop down shows that they should go to Adoc. (shown at top of list, followed by Cdoc, Ddoc, etc).
Patient comes in, the drop down shows that they should go to Cdoc. (shown at top of list, followed by Ddoc, Adoc, etc).
Patient comes in, the drop down shows that they should go to Ddoc. (shown at top of list, followed by Adoc, Cdoc, etc).
Patient comes in, the drop down shows that they should go to Adoc.
Patient comes in, the drop down shows that they should go to Cdoc.
Patient comes in, the drop down shows that they should go to Ddoc.

I hope that makes sense, I've got a simple rotating list going, but having trouble with the "twist".

Thanks!

[FlameRetired]

Hi Code_ and welcome to the forum.

I've got a simple rotating list going, but having trouble with the "twist".

Perhaps this will give you a start.

The drop downs in column C are single choice. To restore to No leave status just hit delete.

All Docs is the default staffing.

Column A has current staffing absent "On leave" docs. It is an array formula entered in A2 and filled down. If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Please Login or Register  to view this content.

Play with the "On leave" status drop downs and watch the changes in "Staffing".

Will this work with your rotating list?

Row\Col	A	B	C
1	Staffing	All Docs	On leave
2	Adoc	Adoc
3	Cdoc	Bdoc	On leave
4	Ddoc	Cdoc
5		Ddoc

[Code_]

Awesome, thank you for the reply! I'll dive in to this and see where I get.

This is exactly what I needed, perfect! Thank you very much.

[FlameRetired]

You are welcome. Thank you for the feedback and the rep.