1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Staff Roster and Report

Discussion in 'Ask an Excel Question' started by Nicki, Oct 6, 2017.

  1. Nicki

    Nicki Member

    Messages:
    39
    Hi Guys.
    I have a Weekly roster for nearly 42 staff working in 30 room in different shift in a week. as you can see in weekly rota tab in Spreadsheet the Initial for staff are on the left side and as I enter any initial in time slot (in red coloured time slot).
    PROBLEM: difficult to see one staff location in week in order not to put her in the same room next week.
    I would like to see a generated report for each staff individual by room allocation so that they are not going to the same room for the following next weeks. any other idea for seeing one staff rota in a week, similar to staff rota tab. Please help. Many Thanks

    Attached Files:

  2. bosco_yip

    bosco_yip Excel Ninja

    Messages:
    1,344
    Try,

    1] In "1 Staff rota" sheet C7, formula copied across and down :

    =IFERROR(INDEX('Weekley Rota'!$B$3:$AM$3,MATCH($E$3,OFFSET('Weekley Rota'!$B$2,MATCH($B7,'Weekley Rota'!$B$2:$B$133,0)+COLUMN(A1)*4-2,,,38),0)),"")

    2] See attached file.

    Regards
    Bosco

    Attached Files:

  3. Nicki

    Nicki Member

    Messages:
    39
    This is brilliant Thank you so much Bosco, Great job,
  4. Nicki

    Nicki Member

    Messages:
    39
    Hi Bosco,

    Sorry If I am bothering you again. In case you have a free time could you look at my new table what is wrong with formula in "Report Tab".

    Many Thanks.

    Nicky

    Attached Files:

  5. bosco_yip

    bosco_yip Excel Ninja

    Messages:
    1,344
    See as per attached revised file.

    Regards
    Bosco

    Attached Files:

    Thomas Kuriakose and Nicki like this.
  6. Nicki

    Nicki Member

    Messages:
    39
    Bosco it is amazing the way you sorting out the formula, you are acting like a chess player. Many Thank. Highly appreciated your help.
  7. Nicki

    Nicki Member

    Messages:
    39
    Hi Bosco

    Please can you amend this formula in order to have G7 column for the 17:50 time slot. Thanks
  8. Nicki

    Nicki Member

    Messages:
    39
    I manage to dot it by adding a row to the weeklyRoster table. Ignore my last post. Many Thanks.
  9. Nicki

    Nicki Member

    Messages:
    39
    Hi Bosco,

    I appreciate if you could help me retrieving background cells (staff initials D5, 9,13,17,20) in "weeklyRoster" (in above attachment- US-OCT 2017 Training) and showing up Report sheet sheet. for example if cell D5 is pink is pink in Report sheet C7. Many Thanks.
  10. Nicki

    Nicki Member

    Messages:
    39
    Hi Bosco

    I have used conditional formatting for changing the cell colour in "All Staff" report tab, by adding a symbol(i.e "*") in cell D6 week 1. But how can i apply it to the all cells? any quickie formula?

    Many Thanks

    Attached Files:

  11. bosco_yip

    bosco_yip Excel Ninja

    Messages:
    1,344
    1] Sheets "Week1" and "Week2", D6:AO154 >> enter: 0 and font color selected "White" to all white ground colored cells.

    p.s. I have done for "Monday" area only, the other areas, Tuesday, Wednesday…. etc. to be done by yourself.

    2] Sheets "Week1" and "Week2", D6:AO154 >> Conditional Formatting >> New rule >> Formula rule :

    =COUNTIF(Full_scanning_team,OFFSET(D6,-1,))

    >> Click "Format" >> "Format Cells" >> select "Number" tag >> choose "Custom" >> in the Type box, enter : "*"

    upload_2017-10-25_13-2-19.png

    and >> select "Fill" tag >> choose ground color : Pink >> OK

    upload_2017-10-25_13-3-6.png
    upload_2017-10-25_13-3-52.png

    >> Finish

    3] See attached file


    Regards
    Bosco

    Attached Files:

  12. Nicki

    Nicki Member

    Messages:
    39
    I am sorry Bosco, I have confused you with my question. I would like to get pink colour in Staff Report Tab, if "*" is typed in any cells under Staff initial (means staff is in training not on duty)in Week1 and 2 cells. I have done it through conditional formatting for one cell see attached. how can i apply it to each cells in Staff Report. Many thanks

    Attached Files:

    • 1.png
      1.png
      File size:
      84.7 KB
      Views:
      3
  13. bosco_yip

    bosco_yip Excel Ninja

    Messages:
    1,344
    1] Conditional formatting with new rule formula has applied to "All Staff" sheet range B5:S45.

    2] See attached revised file.

    Regards
    Bosco

    Attached Files:

    Thomas Kuriakose and Nicki like this.
  14. Nicki

    Nicki Member

    Messages:
    39
    Thank you so much Bosco. Great job as always. Have a great weekend.
  15. Nicki

    Nicki Member

    Messages:
    39
    Hi Bosco, Please could you advice on conditional formatting same as above for "1 Staff" as well please. (see 4th Sheet called "1 Staff" above attachment). Many Thanks

    Attached Files:

    • 1.png
      1.png
      File size:
      33.7 KB
      Views:
      2
  16. bosco_yip

    bosco_yip Excel Ninja

    Messages:
    1,344
    1] Conditional formatting to "1 Staff" sheet, done as per requested.

    2] See attached revised file.

    Regards
    Bosco

    Attached Files:

    Thomas Kuriakose and Nicki like this.
  17. Nicki

    Nicki Member

    Messages:
    39
    It is grrrreat. Many Thanks
  18. Nicki

    Nicki Member

    Messages:
    39
    Bosco could you please let me know how to alphabetically sort staff initial in Week1, AY5:BD11 in the above spreasheet. Many Thanks
  19. bosco_yip

    bosco_yip Excel Ninja

    Messages:
    1,344
    Your request is not clear, cells AY5:BD11 value is selected from Validation List and cannot be sorted ?

    Could your give a file or picture to show what is your expected results.

    Regards
    Bosco
    Last edited: Nov 1, 2017
    Nicki likes this.
  20. Nicki

    Nicki Member

    Messages:
    39
    Yes,they are from validation list, is it possible to sort them A-Z once selected from list within that range AY5:BD11 ?
  21. Nicki

    Nicki Member

    Messages:
    39
    Please see picture below

    Attached Files:

  22. Nicki

    Nicki Member

    Messages:
    39
    Bosco : my aim is to avoid using one initial twice within that range, I was thinking to use sorting A-Z but I think i can use CF duplicate them will solve the problem. I am so sorry I confused you with a stupid question. Have a great day.

Share This Page