Best way to make schedule for multiple physicians

[apatriciao521]

Good Afternoon! I have an excel question that has been driving my CrAzY! I can't figure this out, and am not sure if Macros is the best solution?

What I need to do is this:

- I have created a simple schedule (see copy below) where I can select appointment start times and appt. types from a drop down menu. Depending on the selection of time and appointment type, the remaining columns automatically populate (using IF functions) to tell me how long each patient will be at each stage of the appointment process. For example, a short appointment type will result in 10 minutes of "work up time", 0 minutes of "refract time", 0 minutes of "dilation time", and 5 minutes with the doctor.

[TABLE="width: 909"]
<TBODY>[TR]
[TD]Time</SPAN>
[/TD]
[TD]Appt Type</SPAN>
[/TD]
[TD]Work up Time</SPAN>
[/TD]
[TD]Refract Time</SPAN>
[/TD]
[TD]Dilation Time</SPAN>
[/TD]
[TD]Total Minutes</SPAN>
[/TD]
[TD]Ready</SPAN>
[/TD]
[TD]MD In</SPAN>
[/TD]
[TD]MD Out</SPAN>
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8:00 AM</SPAN>
[/TD]
[TD]Long</SPAN>
[/TD]
[TD]15</SPAN>
[/TD]
[TD]10</SPAN>
[/TD]
[TD]15</SPAN>
[/TD]
[TD]40</SPAN>
[/TD]
[TD]8:40 AM</SPAN>
[/TD]
[TD]8:40 AM</SPAN>
[/TD]
[TD]8:50 AM</SPAN>
[/TD]
[/TR]
[TR]
[TD]8:10 AM</SPAN>
[/TD]
[TD]Long</SPAN>
[/TD]
[TD]15</SPAN>
[/TD]
[TD]10</SPAN>
[/TD]
[TD]15</SPAN>
[/TD]
[TD]40</SPAN>
[/TD]
[TD]8:50 AM</SPAN>
[/TD]
[TD]8:50 AM</SPAN>
[/TD]
[TD]9:00 AM</SPAN>
[/TD]
[/TR]
[TR]
[TD]8:30 AM</SPAN>
[/TD]
[TD]Medium</SPAN>
[/TD]
[TD]10</SPAN>
[/TD]
[TD]0</SPAN>
[/TD]
[TD]15</SPAN>
[/TD]
[TD]25</SPAN>
[/TD]
[TD]8:55 AM</SPAN>
[/TD]
[TD]9:00 AM</SPAN>
[/TD]
[TD]9:10 AM</SPAN>
[/TD]
[/TR]
[TR]
[TD]8:30 AM</SPAN>
[/TD]
[TD]Long</SPAN>
[/TD]
[TD]15</SPAN>
[/TD]
[TD]10</SPAN>
[/TD]
[TD]15</SPAN>
[/TD]
[TD]40</SPAN>
[/TD]
[TD]9:10 AM</SPAN>
[/TD]
[TD]9:10 AM</SPAN>
[/TD]
[TD]9:20 AM</SPAN>
[/TD]
[/TR]
[TR]
[TD]9:00 AM</SPAN>
[/TD]
[TD]Short</SPAN>
[/TD]
[TD]10</SPAN>
[/TD]
[TD]0</SPAN>
[/TD]
[TD]0</SPAN>
[/TD]
[TD]10</SPAN>
[/TD]
[TD]9:10 AM</SPAN>
[/TD]
[TD]9:20 AM</SPAN>
[/TD]
[TD]9:25 AM</SPAN>
[/TD]
[/TR]
[TR]
[TD]9:00 AM</SPAN>
[/TD]
[TD]Medium</SPAN>
[/TD]
[TD]10</SPAN>
[/TD]
[TD]0</SPAN>
[/TD]
[TD]15</SPAN>
[/TD]
[TD]25</SPAN>
[/TD]
[TD]9:25 AM</SPAN>
[/TD]
[TD]9:25 AM</SPAN>
[/TD]
[TD]9:35 AM</SPAN>
[/TD]
[/TR]
[TR]
[TD]9:00 AM</SPAN>
[/TD]
[TD]Long</SPAN>
[/TD]
[TD]15</SPAN>
[/TD]
[TD]10</SPAN>
[/TD]
[TD]15</SPAN>
[/TD]
[TD]40</SPAN>
[/TD]
[TD]9:40 AM</SPAN>
[/TD]
[TD]9:40 AM</SPAN>
[/TD]
[TD]9:50 AM</SPAN>
[/TD]
[/TR]
[TR]
[TD]9:35 AM</SPAN>
[/TD]
[TD]Short</SPAN>
[/TD]
[TD]10</SPAN>
[/TD]
[TD]0</SPAN>
[/TD]
[TD]0</SPAN>
[/TD]
[TD]10</SPAN>
[/TD]
[TD]9:45 AM</SPAN>
[/TD]
[TD]9:50 AM</SPAN>
[/TD]
[TD]9:55 AM</SPAN>
[/TD]
[/TR]
[TR]
[TD]9:40 AM</SPAN>
[/TD]
[TD]Short</SPAN>
[/TD]
[TD]10</SPAN>
[/TD]
[TD]0</SPAN>
[/TD]
[TD]0</SPAN>
[/TD]
[TD]10</SPAN>
[/TD]
[TD]9:50 AM</SPAN>
[/TD]
[TD]9:55 AM</SPAN>
[/TD]
[TD]10:00 AM</SPAN>
[/TD]
[/TR]
[TR]
[TD]9:40 AM</SPAN>
[/TD]
[TD]Medium</SPAN>
[/TD]
[TD]10</SPAN>
[/TD]
[TD]0</SPAN>
[/TD]
[TD]15</SPAN>
[/TD]
[TD]25</SPAN>
[/TD]
[TD]10:05 AM</SPAN>
[/TD]
[TD]10:05 AM</SPAN>
[/TD]
[TD]10:15 AM</SPAN>
[/TD]
[/TR]
[TR]
[TD]9:45 AM</SPAN>
[/TD]
[TD]Long</SPAN>
[/TD]
[TD]15</SPAN>
[/TD]
[TD]10</SPAN>
[/TD]
[TD]15</SPAN>
[/TD]
[TD]40</SPAN>
[/TD]
[TD]10:25 AM</SPAN>
[/TD]
[TD]10:25 AM</SPAN>
[/TD]
[TD]10:35 AM</SPAN>
[/TD]
[/TR]
[TR]
[TD]9:50 AM</SPAN>
[/TD]
[TD]Long</SPAN>
[/TD]
[TD]15</SPAN>
[/TD]
[TD]10</SPAN>
[/TD]
[TD]15</SPAN>
[/TD]
[TD]40</SPAN>
[/TD]
[TD]10:30 AM</SPAN>
[/TD]
[TD]10:35 AM</SPAN>
[/TD]
[TD]10:45 AM</SPAN>
[/TD]
[/TR]
[TR]
[TD]10:05 AM</SPAN>
[/TD]
[TD]Medium</SPAN>
[/TD]
[TD]10</SPAN>
[/TD]
[TD]0</SPAN>
[/TD]
[TD]15</SPAN>
[/TD]
[TD]25</SPAN>
[/TD]
[TD]10:30 AM</SPAN>
[/TD]
[TD]10:45 AM</SPAN>
[/TD]
[TD]10:55 AM</SPAN>
[/TD]
[/TR]
[TR]
[TD]10:10 AM</SPAN>
[/TD]
[TD]Long</SPAN>
[/TD]
[TD]15</SPAN>
[/TD]
[TD]10</SPAN>
[/TD]
[TD]15</SPAN>
[/TD]
[TD]40</SPAN>
[/TD]
[TD]10:50 AM</SPAN>
[/TD]
[TD]10:55 AM</SPAN>
[/TD]
[TD]11:05 AM</SPAN>
[/TD]
[/TR]
</TBODY>[/TABLE]

What I need in addition to this scheduling worksheet is this: I have five technicians that work at my office and are responsible for the "work-up" and "refract" portions of the appointments. I need to be able to somehow create a link between the scheduling tab to a new worksheet where I can see which technician is busy with an appointment throughout the day and where we have empty time slots to fill.

For example, if the 8am appointment has 10 minutes of work-up time and 10 mintes of refract time than technician #1 is occupied for 20 minutes, leaving technician #2-5 available. But if there are two appointments at 8am, then only technician #3-5 are available and so forth. What I have now is the "resource" schedule (see example below) where I manually color code the time slots that are occupied (for example a 10 minute appointment starting at 8am would be shaded from 8-8:10, and so on); I would like this formatting to be automated!

[TABLE="width: 420"]
<TBODY>[TR]
[TD][/TD]
[TD]Tech 1</SPAN>
[/TD]
[TD]Tech 2</SPAN>
[/TD]
[TD]Tech 3</SPAN>
[/TD]
[TD]Tech 4</SPAN>
[/TD]
[/TR]
[TR]
[TD]8:05</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8:10</SPAN>
[/TD]
[TD]AJG Long</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8:15</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8:20</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8:25</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8:30</SPAN>
[/TD]
[TD]AJG Long</SPAN>
[/TD]
[TD]AJG intermed</SPAN>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8:35</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8:40</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8:45</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8:50</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8:55</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9:00</SPAN>
[/TD]
[TD]AJG Long</SPAN>
[/TD]
[TD]AJG intermed</SPAN>
[/TD]
[TD]AJG short</SPAN>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9:05</SPAN>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9:10</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9:15</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9:20</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9:25</SPAN>
[/TD]
[TD]AJG short</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9:30</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9:35</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9:40</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD]AJG short</SPAN>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9:45</SPAN>
[/TD]
[TD]AJG Long</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9:50</SPAN>
[/TD]
[TD]AJG Long</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9:55</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10:00</SPAN>
[/TD]
[TD][/TD]
[TD]AJG intermed</SPAN>
[/TD]
[/TR]
[TR]
[TD]10:05</SPAN>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10:10</SPAN>
[/TD]
[TD][/TD]
[TD]AJG Long</SPAN>
[/TD]
[/TR]
[TR]
[TD]10:15</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10:20</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10:25</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10:30</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]

Basically I need to find a way to schedule each of my technicians with each appointment as needed. So if technician #1 is busy, and technician number #2 is busy than technician #3 is available for the time slot needed for the set appointment. I want to be able to visually see this all.

I am not sure if this made any sense! But can anyone help or point me in the right direction of how to solve this problem? Right now, this process takes hours of manually labor each day!

Thank you!!!

 

[apatriciao521]

Can anyone help me with this? I am new to Mr. Excel but am hoping that someone can point me in the right direction! Thank you again!