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

Messages:
46
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

File size:
68.8 KB
Views:
22
2. bosco_yipExcel Ninja

Messages:
1,799
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

File size:
66.8 KB
Views:
13
3. NickiMember

Messages:
46
This is brilliant Thank you so much Bosco, Great job,
4. NickiMember

Messages:
46
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

File size:
413.5 KB
Views:
8
5. bosco_yipExcel Ninja

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

Regards
Bosco

Attached Files:

• US- Oct 2017 Training TEMPLATES - with working lists (2a).xls
File size:
693 KB
Views:
18
Thomas Kuriakose and Nicki like this.
6. NickiMember

Messages:
46
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. NickiMember

Messages:
46
Hi Bosco

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

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

Messages:
46
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. NickiMember

Messages:
46
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

File size:
765 KB
Views:
7
11. bosco_yipExcel Ninja

Messages:
1,799
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 : "*"

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

>> Finish

3] See attached file

Regards
Bosco

File size:
874.5 KB
Views:
6
12. NickiMember

Messages:
46
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

File size:
84.7 KB
Views:
3
13. bosco_yipExcel Ninja

Messages:
1,799
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:

• 2 Weeks Roster(2).xlsx
File size:
164.8 KB
Views:
8
Thomas Kuriakose and Nicki like this.
14. NickiMember

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

Messages:
46
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

File size:
33.7 KB
Views:
3
16. bosco_yipExcel Ninja

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

2] See attached revised file.

Regards
Bosco

Attached Files:

• 2 Weeks Roster(3).xlsx
File size:
166 KB
Views:
14
Thomas Kuriakose and Nicki like this.
17. NickiMember

Messages:
46
It is grrrreat. Many Thanks
18. NickiMember

Messages:
46
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_yipExcel Ninja

Messages:
1,799
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. NickiMember

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

Messages:
46

File size:
86 KB
Views:
7
22. NickiMember

Messages:
46
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.