Excel help with scheduling!

So, Ive made a 6-week schedule for my hospital and I need to have it generate a daily schedule.

So the nurses would have a "A" or "P" beside their name under the corresponding date. Based on the date of the last spreadsheet "housewide", I'd like excel to pull the nurses' names and populate it to the corresponding columns.

Ideas?

I don't understand. I don't see any names in sheet "Housewide". I don't know why they would get an "A" or a "P".

Ok so that was totally me fault.

So on the first 9 pages (2nd, 3rd, ICU, ED ect...) the nurse scheduler will enter an "A" or "P" depending on what shift the nurse signs up for.(A = 7am-7pm, P = 7pm-7am. This is a standard 6 week schedule.

The final page "Housewide" is a DAILY schedule that I would like excel to generate.

So, looking at the last page, you can see 2 med/surg, 3 med/surg, ED, Floatpool, ICU ect.....under those headings youll see "RN" "NA" Ect.

So basically, Im trying to figure out how to get excel to recognize the date on the housewide sheet....then reference the grids on the other 9 sheets...recognize the A or P and then populate the nurses names to the daily sheet.

right now the nurses names are "test"

Well, here's a start anyway. I just took one section and it works as best I can tell. The section is under "RN" for 2 MED/SURG. You'll see 4 nurses there now. If you change the date at the top to 11/30, you'll see the list of nurses change. I put this formula in Cell A8 (on the Housewide sheet). This is an ARRAY formula, meaning you have to hit Shift/Control/Enter instead of just ENTER. (It will put brackets {} around the formula).

=IFERROR(INDEX('2nd'!$B$6:$B$20,SMALL(IF(OFFSET('2nd'!$C$6:$C$20,0,MATCH($A$3,'2nd'!$C$4:$AR$4,0)-1)="a",ROW(OFFSET('2nd'!$C$6:$C$20,0,MATCH($A$3,'2nd'!$C$4:$AR$4,0)-1))-ROW(INDEX(OFFSET('2nd'!$C$6:$C$20,0,MATCH($A$3,'2nd'!$C$4:$AR$4,0)-1),1,1))+1),ROW()-7)),"")

You can then drag this formula down to A15.

Let me know if this gets it on the right track.

I'm not even going to lie to you.....this makes me want to cry. THANK YOU SOOOO MUCH!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

Glad it helped! :-)

You've got quite a bit of work to do yet though :-)

Good luck!

Greg, look at this code for the NA section...

{=IFERROR(INDEX('2nd'!$B$26:$B$34,SMALL(IF(OFFSET('2nd'!$C$26:$C$34,0,MATCH($A$3,'2nd'!$C$25:$AR$25,0)-1)="a",ROW(OFFSET('2nd'!$C$26:$C$34,0,MATCH($A$3,'2nd'!$C$25:$AR$25,0)-1))-ROW(INDEX(OFFSET('2nd'!$C$26:$C$34,0,MATCH($A$3,'2nd'!A4:$C$25,0)-1),1,1))+1),ROW()-7)),"")}

I dont get any errors but nothing populates....what am i missing?

{=IFERROR(INDEX('2nd'!$B$26:$B$34,SMALL(IF(OFFSET('2nd'!$C$26:$C$34,0,MATCH($A$3,'2nd'!$C$25:$AR$25,0)-1)="a",ROW(OFFSET('2nd'!$C$26:$C$34,0,MATCH($A$3,'2nd'!$C$25:$AR$25,0)-1))-ROW(INDEX(OFFSET('2nd'!$C$26:$C$34,0,MATCH($A$3,'2nd'!A4:$C$25,0)-1),1,1))+1),ROW()-25)),"")}

sorry this is the code i was trying....

So that worked.....other than $C$25:$AR$25 and the row drop....what did you change? Am I missing something...I swore I changed that before lol!!!!

The section in Red:

=IFERROR(INDEX('2nd'!$B$26:$B$34,SMALL(IF(OFFSET('2nd'!$C$26:$C$34,0,MATCH($A$3,'2nd'!$C$25:$AR$25,0)-1)="a",ROW(OFFSET('2nd'!$C$26:$C$34,0,MATCH($A$3,'2nd'!$C$25:$AR$25,0)-1))-ROW(INDEX(OFFSET('2nd'!$C$26:$C$34,0,MATCH($A$3,'2nd'!A4:$C$25,0)-1),1,1))+1),ROW()-25)),"")

How in the heck did I miss that?!


Once again sir, you are the best! Thank you!!!!