Trying to make an MD resident schedule

**razzy8** · 05-19-2009, 05:15 PM

Hi,

I'm new to this forum and I feel pretty lucky to have found that such a place exists

I have been using Excel for a pretty long time now (and have even taken a night course), but consider myself pretty novice....so bear with me

I think that this is a bit of a confusing problem, but I am sure that it is possible (with some expert help

) and can be done!!!!

I have attached a dummy schedule (names changed) to make it easier for someone to try to help

Here is what I would like to do:

I am trying to make a clinic schedule for MD residents for each consecutive month.
Each resident rotates through a different specialty each month.
On the worksheet called "template" is a template for when the resident should be in clinic (C).
Then on the worksheet called "July" is my first attempt at the schedule. What I already did was copy/past from the "template" worksheet to the "July" worksheet based on what rotation the resident is on A7:28.
The problem is that instead of copy/pasting each time I would like Excel to look at each resident in "Aug" B7:28 for instance, see what rotation they are in A7:28, look on the "template " match the rotation and paste the row for when they are in clinic for that rotation onto the August worksheet.

I hope that makes sense...I tried to explain it as best as I could.

THANK YOU in advance for all your help...someone will make my scheduling nightmare a dream

-Razzy

**shg** · 05-20-2009, 06:50 PM

The problem is that instead of copy/pasting each time I would like Excel to look at each resident in "Aug" B7:28 for instance, see what rotation they are in A7:28, look on the "template " match the rotation and paste the row for when they are in clinic for that rotation onto the August worksheet.

Would you type that part in again slowly?

**split_atom18** · 05-21-2009, 12:54 PM

Template has a 5 week rotation, is that continous?

Ie does every 5 weeks it starts over?

**razzy8** · 05-21-2009, 10:45 PM

Hey split_atom18 & shg,

Thanks for your responses!

split_atom18,
Well, I make the template with 5 weeks since I thought it would make it easier for excel to cut/paste. Basically I don't have to worry about weekends and so the most number of weekdays any given month can have is 5days x 4weeks=20 plus the week can start on any day from month so I put in another dummy week (on each month from July to June after excel pastes the rows I would put in the date corresponding to when the month starts.

shg,
Ok, I will try to explain more clearly what I am trying to do.
Basically I need to make up a clinic schedule for the next year.
Each month the residents are on different rotations, these rotations change at the end of each month.
Depending on the rotation, the residents will be in clinic on set days of the week. IE on Dermatology a resident may only be in clinic Monday AM/PM, Wed PM, Friday AM/PM.
I have created a template worksheet with each rotation and when the resident is scheduled to be in clinic.
I then made a worksheet for each month and I will put the rotation each resident is on next to their name (animals in the dummy template).
I basically want Excel to look at July worksheet and say...lets see..."resident A" is on Dermatology...lets see when should resident A on Dermatology be in clinic...ok...look at the template worksheet...ok...Monday AM/PM, Wed PM, Friday AM/PM...boom...paste that row onto the July worksheet next to that resident's name.
Then go down the column and do each resident one by one and paste the rows for each resident corresponding to the rotation they are on.
I hope that wasn't too long winded...I am sure that there is a "relatively" easy if/then or lookup formula that can be used I just don't know how to implement them nor how to tell the formulas to look in different worksheets.
So I've basically already done July by copying and pasting the rows manually...now I would like to automate the process for Aug-June

Thank again you everyone in advance!!

**razzy8** · 05-31-2009, 09:53 PM

I am not getting any bites on this question....so I thought I would simplify the problem I am trying to solve.

Basically I would like excel to match the rotation from one worksheet (July) to the other worksheet (Template) then paste the rows corresponding to that rotation on the original worksheet (July).

I think it would need to use a lookup function or find, replace...I just don't know which function would be best, let alone how to write it.

Thanks in advance for your help!
-David

PS: MODERATORS: do you think this thread is in the wrong category (Excel - New Users) and it would get better response in the "Excel General" category?

**split_atom18** · 06-01-2009, 01:00 PM

Me personally, I would make a sheet with 365 columns, it would contain every day of the year, that is where I would enter in the schedule. Then I would use the sheets you have for months using Vlookup to pull the appropriate data from the "Template of 365 days" so you have monthly schedules.

Hope this helps you some,

Dan

**squiggler47** · 06-01-2009, 01:27 PM

Assuming your template is like that

you can use =index(TemplateRange,match(Department,DepartmentRange,0),rowTouse)

**squiggler47** · 06-01-2009, 01:28 PM

Your solution wouldnt work split atom, for 1 very simple reason!

Excel 2003 only has 256 columns!

**split_atom18** · 06-01-2009, 05:51 PM

DOH! Good catch, You could always have 2 sheets then, first 6 months second 6 months.

Or

One sheet, 2 rows deep basically have 2 sets of rows for each department stacked.

Sorry should have looked at the versions.

Thanks,

Dan

**razzy8** · 06-16-2009, 10:41 PM

thanks for your reply split_atom & squiggler,

The problem is, I can't make my template 365 days (nor 1/2 the year) because each resident (animal) only spends 1 month on each rotation.

I need to be able to tell excel to match the resident to the rotation and then paste the row from the template onto the month.

HELP...there must be a solution to this?!

Thanks in advance again,
-Razzy