+ Reply to Thread
Results 1 to 13 of 13

Excel help with scheduling!

  1. #1
    Registered User
    Join Date
    11-28-2019
    Location
    North Carolina
    MS-Off Ver
    Excel 2013
    Posts
    7

    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?
    Attached Files Attached Files

  2. #2
    Forum Contributor
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    265

    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. #3
    Registered User
    Join Date
    11-28-2019
    Location
    North Carolina
    MS-Off Ver
    Excel 2013
    Posts
    7

    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. #4
    Forum Contributor
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    265

    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.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    11-28-2019
    Location
    North Carolina
    MS-Off Ver
    Excel 2013
    Posts
    7

    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. #6
    Forum Contributor
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    265

    Re: Excel help with scheduling!

    Glad it helped! :-)

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

    Good luck!

  7. #7
    Registered User
    Join Date
    11-28-2019
    Location
    North Carolina
    MS-Off Ver
    Excel 2013
    Posts
    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. #8
    Registered User
    Join Date
    11-28-2019
    Location
    North Carolina
    MS-Off Ver
    Excel 2013
    Posts
    7

    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. #9
    Forum Contributor
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    265

    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))
    Last edited by Gregb11; 12-02-2019 at 10:45 PM. Reason: add explanation

  10. #10
    Registered User
    Join Date
    11-28-2019
    Location
    North Carolina
    MS-Off Ver
    Excel 2013
    Posts
    7

    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. #11
    Forum Contributor
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    265

    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. #12
    Registered User
    Join Date
    11-28-2019
    Location
    North Carolina
    MS-Off Ver
    Excel 2013
    Posts
    7

    Re: Excel help with scheduling!

    How in the heck did I miss that?!


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

  13. #13
    Forum Moderator Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux
    Posts
    8,606

    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.
    You really don't have to give me rep points for moderating. It's part of the job. Keep rep points for those who find solutions

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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