# Excel help with scheduling!

1. ## 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?

2. ## Re: Excel help with scheduling!

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".

3. ## Re: Excel help with scheduling!

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"

4. ## Re: Excel help with scheduling!

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.

5. ## Re: Excel help with scheduling!

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

6. ## Re: Excel help with scheduling!

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

Good luck!

7. ## Re: Excel help with scheduling!

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?

8. ## Re: Excel help with scheduling!

{=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....

9. ## Re: Excel help with scheduling!

Try this - again, as an array formula - shift/ctrl/Enter

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

(Of course where I have \$C\$4:\$AR\$4 it could also be (and probably more appropriately be \$C\$25:\$AR\$25))

10. ## Re: Excel help with scheduling!

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!!!!

11. ## Re: Excel help with scheduling!

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)),"")

12. ## Re: Excel help with scheduling!

How in the heck did I miss that?!

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

13. ## Re: Excel help with scheduling!

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1