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

  • 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

N

Nicki

Member
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
 

Attachments

  • Roster-US.xlsx
    68.8 KB · Views: 25
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
 

Attachments

  • Roster-US(1).xlsx
    66.8 KB · Views: 20
Nicki said:
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
Click to expand...
bosco_yip said:
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
Click to expand...

This is brilliant Thank you so much Bosco, Great job,
 
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
 

Attachments

  • US- Oct 2017 Training TEMPLATES - with working lists (2).xls
    413.5 KB · Views: 9
Bosco it is amazing the way you sorting out the formula, you are acting like a chess player. Many Thank. Highly appreciated your help.
 
bosco_yip said:
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
Click to expand...

Hi Bosco

Please can you amend this formula in order to have G7 column for the 17:50 time slot. Thanks
 
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.
 
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
 

Attachments

  • 2 Weeks Roster.xls
    765 KB · Views: 9
Nicki said:
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
Click to expand...

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
 

Attachments

  • 2 Weeks Roster(1).xls
    874.5 KB · Views: 6
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
 

Attachments

  • 1.png
    1.png
    84.7 KB · Views: 3
Nicki said:
...... 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. .........
Click to expand...

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

2] See attached revised file.

Regards
Bosco
 

Attachments

  • 2 Weeks Roster(2).xlsx
    164.8 KB · Views: 8
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
 

Attachments

  • 1.png
    1.png
    33.7 KB · Views: 4
Nicki said:
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
Click to expand...

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

2] See attached revised file.

Regards
Bosco
 

Attachments

  • 2 Weeks Roster(3).xlsx
    166 KB · Views: 18
Bosco could you please let me know how to alphabetically sort staff initial in Week1, AY5:BD11 in the above spreasheet. Many Thanks
 
Nicki said:
Bosco could you please let me know how to alphabetically sort staff initial in Week1, AY5:BD11 in the above spreasheet. Many Thanks
Click to expand...

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:
Yes,they are from validation list, is it possible to sort them A-Z once selected from list within that range AY5:BD11 ?
 
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.
 
You must log in or register to reply here.
Back
Top