+ Reply to Thread
Results 1 to 46 of 46

Shade Cells in Staff Rota if Staff Are at Work

  1. #1
    Registered User
    Join Date
    08-04-2020
    Location
    London
    MS-Off Ver
    2019
    Posts
    25

    Shade Cells in Staff Rota if Staff Are at Work

    Hi Guys

    I'm new here and very new to Excel so apologies in advance for being a newbie and a novice!

    I have been trying to create a couple of spreadsheets to help save me time and manage my small team. I have been successful on creating a working spreadsheet but on this one I need some assistance as not too sure where to start creating formulas.

    I need to create a staff timetable so at a glance I can see quickly who is in on what day - so I will have a table for each day with the staff names and then I would like the times to be shaded with a colour if they are in... if that makes sense?

    I have been given a template from a friend which was a help but has no formulas I have attached it for you guys to see and hopefully then this will make more sense...

    So, there 2 shift patterns a Week A then a Week B I have created a tab for each week. I have then created a tab for the Rota where I will enter the hours that the staff member will be doing in Week A and Week B and I would then like it to shade on the Week A/B tabs in the colour depending on their task, is this possible? I know I may have to alter the spreadsheet possible but I have no idea what I will need to change or how to write the formula needed,

    Any help will be appreciated and many thanks in advance. If this is the wrong forum for asking for help like this please say.

    Thanks
    Freddy.
    Attached Files Attached Files
    Last edited by AliGW; 08-04-2020 at 03:26 AM.

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Help with Excel Spreadsheet - formulas

    Administrative Note:

    Welcome to the forum.

    We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do, not how you think it should be done. Almost everyone here wants help with an Excel spreadsheet formula!!!

    Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

    Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

    (Note: this change is not optional. As you are new, I will do it for you this time.)
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Shade Cells in Staff Rota if Staff Are at Work

    You will need to show us what you want. Manually shade the first 10-15 rows of the WEEK A tab to illustrate your requirements.

  4. #4
    Registered User
    Join Date
    08-04-2020
    Location
    London
    MS-Off Ver
    2019
    Posts
    25

    Re: Shade Cells in Staff Rota if Staff Are at Work

    Hi Ali

    Thank you for your response and my apologies for being a newbie and an Excel novice.

    I have ammended my sample spreadsheet to display how I would like the data from the rota tab to show in the Week A tab - I hope this helps? If for any reason it's not clear or you need more info from me please let me know.

    I will also need to have a table for each day of the week in the tabs Week A and B I guess there is a way to copy and paste keeping the same format/layout?

    Thanks again for your help and support.

    Freddy.
    Attached Files Attached Files
    Last edited by AliGW; 08-04-2020 at 04:34 AM. Reason: Please don't quote unnecessarily!

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Shade Cells in Staff Rota if Staff Are at Work

    You do not need to apologise for being new to this - nobody can get anywhere without a beginning!

    I will have a look at the updated workbook now.

  6. #6
    Registered User
    Join Date
    08-04-2020
    Location
    London
    MS-Off Ver
    2019
    Posts
    25

    Re: Shade Cells in Staff Rota if Staff Are at Work

    Thank you Ali, I greatly appreciate your help, support and understanding
    Last edited by AliGW; 08-04-2020 at 04:43 AM. Reason: Please don't quote unnecessarily!

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Shade Cells in Staff Rota if Staff Are at Work

    OK, so immediately there is a problem of which you won't yet be aware. Unfortunately, Excel cannot read formatting, so colour coding tasks on the main sheet and expecting the week tabs to be able to pick this up just isn't going to work.

    You have two options, really:

    1. Consider going down the VBA route. However, this might take more time for you to learn to manage, and VBA will not work if you have to use these workbooks in the online browser environment, so you need to consider this.
    2. If VBA is not a viable option for you, then you need some way of identifying the different tasks in the master sheet.

    The master sheet is not really laid out in an optimum way for data analysis and for pulling the information you want to the weekly sheets. You are putting aesthetics before functionality, and that's a rookie mistake (we've almost all been there and got the T-shirt). Ideally your master sheet would be in a normalised format with columns for the staff member ID, week, day, task number, start time and end time - one row per record, as in a flat-file database. Form this you can then more easily build your visuals.

    I hope this makes some sort of sense. Get this right NOW and it will save you a lot of headaches in the future.

    Administrative Note:

    Please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!

    For normal conversational replies, try using the QUICK REPLY box below or the REPLY button instead of REPLY WITH QUOTE.
    Last edited by AliGW; 08-04-2020 at 04:46 AM.

  8. #8
    Registered User
    Join Date
    08-04-2020
    Location
    London
    MS-Off Ver
    2019
    Posts
    25

    Re: Shade Cells in Staff Rota if Staff Are at Work

    Thanks for you reply Ali, and apologies for the use of reply with quote option.

    Ok this seems all way out of my depth as I dont know what VBA is - I could get around not having the colour coding that wouldnt be too much of an issue as the important thing was to get the staff hours pulled through.

    The master sheet was given to me by someone else as I didnt know where to start by building a spreadsheet with 15 minute increments of time etc...

    Thank you for your help and assistance but I think it's back to using pen and paper for this task as I wouldnt know where to start

    Thanks again

    Freddy

  9. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Shade Cells in Staff Rota if Staff Are at Work

    Forgive my bluntness (I am a teacher by profession), but a defeatist attitude will get you nowhere. We can only help you to move yourself forward if you are prepared to have a go. Obviously we aren't going to do it for you, as that wouldn't teach you anything, but if you are just going to throw in the towel, well that's that really, isn't it? I'm a bit disappointed, especially having started to explain to you how you could better collate your master data.

    So, if that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

  10. #10
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,409

    Re: Shade Cells in Staff Rota if Staff Are at Work

    I almost forget how to use pen and paper .
    LOL (just kidding)
    Quang PT

  11. #11
    Registered User
    Join Date
    08-04-2020
    Location
    London
    MS-Off Ver
    2019
    Posts
    25

    Re: Shade Cells in Staff Rota if Staff Are at Work

    Sorry Ali, its not that I am being a defeatist at all I just feel that being a novice at this I didnt want to be keep asking questions from you guys and becoming an annoyance to you.

    I would not expect you to do it for me as you say that would teach me nothing and I totally agree as moving forward I would need to understand what I am doing. I did say thank you for your help and assistance and I truly meant that. I just feel that maybe I am too much of a novice for this forum if thats not the case I do apologise.

    I am not going to give in as it's not in my nature to do that my comment of going back to pen and paper was purely a joke at myself being a novice, I apologise for how that may have came accross.

    I obviously need to learn what Excel can do and what it can do for me as you can see I didnt realise that it couldnt do initially what I thought it would... I guess I should have started with a blank Excel sheet rather than inheritting one...

    My plan is now to start with a blank sheet so I can try and build up from that and keep it simple, I will need to know how I set the times like in the previous one to be in 15 minute increments. I am thinking if I have the 2 tabs as before for Week A and B and a Rota tab also as before would I be able just to pull in the data and shade when the staff members are working? I'm not to worried about the colour coding I can work around not having that...

    If you are able to help and really dont mind me asking questions that will seem to be ridiculous I would really love the support but like I say I really dont want to become an annoyance.

    I again apologise for how my previous reply came accross as it was far from how it was taken.

    If you are able to help and if you really sure that it's ok for me being such a novice I would greatly appreciate any help.

    Thanks
    Freddy

  12. #12
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Shade Cells in Staff Rota if Staff Are at Work

    I didnt want to be keep asking questions from you guys and becoming an annoyance to you.
    What??? But this is a HELP forum - it's what we do!!!

    and a Rota tab also as before
    Bad idea. If you aren't prepared to change the rota sheet, then I'm out as I really don't have time to help you to build the complex formulae needed to get the data from there into the week tabs - sorry.

    To be clear: I am not annoyed, and we will field any number of your questions (that's why we are here, after all), but if you are not willing to be guided in a different direction, then I am not sure that I can work with you. Sorry.
    Last edited by AliGW; 08-04-2020 at 05:41 AM.

  13. #13
    Registered User
    Join Date
    08-04-2020
    Location
    London
    MS-Off Ver
    2019
    Posts
    25

    Re: Shade Cells in Staff Rota if Staff Are at Work

    Hi Ali

    I know you are a help forum and I totally appreciate that - I am new here and I don't know if you are a help forum for people at all levels or users past the novice stage. This why I say I dont wont to become an annoyance but say I would greatly appreciate any help if I am ok to be here - which I can see now I am ok to be here.

    I am more than happy to change the rota sheet to however it needs to be, what I was saying was I thought it was best to start with a blank sheet/s and create the Week A/B sheets and then start with a blank rota sheet so everything will be from blank sheets and build up from there so we arent working with any old data/sheets.

    I am more that willing to be guided and that is what I was hoping that you guys would be able to do for me.

    Do you agree that I need to start with blank sheets so we can build from scratch?

    Thank you
    Freddy

  14. #14
    Registered User
    Join Date
    08-04-2020
    Location
    London
    MS-Off Ver
    2019
    Posts
    25

    Re: Shade Cells in Staff Rota if Staff Are at Work

    Sorry Ali, just reading over your replies again and I think that you are saying not to have the data tab? I am happy to take any advice and suggestions I dont need a tab for a rota if you have alternative ideas? It was just purely me thinking thats how it would need to be done sorry...

  15. #15
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Shade Cells in Staff Rota if Staff Are at Work

    No - all you need to do is create a master sheet in the way I have suggested, then we can take it from there. You do NOT need to worry at this stage about the time increments, just the start and end times.

    Here's how it will look:

    Excel 2016 (Windows) 32 bit
    A
    B
    C
    D
    E
    1
    Name Week Day Start End
    2
    Staff A A Sun
    10:30
    12:00
    3
    Staff A A Sat
    11:15
    18:45
    4
    Staff B A Sun
    09:00
    16:15
    5
    Staff B A
    Sheet: Rota

  16. #16
    Registered User
    Join Date
    08-04-2020
    Location
    London
    MS-Off Ver
    2019
    Posts
    25

    Re: Shade Cells in Staff Rota if Staff Are at Work

    Thank you so much Ali, I'll get that created with some sample data and get back to you really do appreciate your help.

  17. #17
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Shade Cells in Staff Rota if Staff Are at Work

    That's what I like to hear!

  18. #18
    Registered User
    Join Date
    08-04-2020
    Location
    London
    MS-Off Ver
    2019
    Posts
    25

    Re: Shade Cells in Staff Rota if Staff Are at Work

    Hi Ali

    I have created a sheet with some sample data as per your decription - I havent tried to format any cells yet- I will be guided by you so I will then have better understanding.

    Thanks
    Freddy
    Attached Files Attached Files

  19. #19
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Shade Cells in Staff Rota if Staff Are at Work

    Thanks!

    I have to go for a while - if nobody else steps in, I'll have a look later, but I am sure others will be looking at this and will jump in.

  20. #20
    Registered User
    Join Date
    08-04-2020
    Location
    London
    MS-Off Ver
    2019
    Posts
    25

    Re: Shade Cells in Staff Rota if Staff Are at Work

    Thank you Ali, I will await to hear back from you or one of your other forum members - thanks again

  21. #21
    Registered User
    Join Date
    08-04-2020
    Location
    London
    MS-Off Ver
    2019
    Posts
    25

    Re: Shade Cells in Staff Rota if Staff Are at Work

    Hi Ali, just wondered if you had a chance to look at the data in the Excel sheet that I sent

  22. #22
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Shade Cells in Staff Rota if Staff Are at Work

    Hi, Freddy!

    No, I am afraid I haven't and am not likely to have much time today or tomorrow, I am afraid, so hopefully someone else can help you with it. I'll put out a call for help.

    EDIT: I've asked for someone else to step in. I'm sure someone will.
    Last edited by AliGW; 08-05-2020 at 04:23 AM.

  23. #23
    Registered User
    Join Date
    08-04-2020
    Location
    London
    MS-Off Ver
    2019
    Posts
    25

    Re: Shade Cells in Staff Rota if Staff Are at Work

    No problem Ali, I understand that you are busy and I can imagine now must be a really busy time for you with getting ready for the new school year. There is no real rush for this. Thank you

  24. #24
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Shade Cells in Staff Rota if Staff Are at Work

    That's exactly the issue! Still working on the timetable - we have about three versions on the go as we really aren't quite sure how it's going to pan out, so it's three times the amount of work and taking far more of my holiday time than usual - grrr!!!

  25. #25
    Registered User
    Join Date
    08-04-2020
    Location
    London
    MS-Off Ver
    2019
    Posts
    25

    Re: Shade Cells in Staff Rota if Staff Are at Work

    Oh no, must be a complete nightmare and it will be such a challenge for you when your students come back especially with the current climate. I hope all goes well for you when you are back in the classroom and all pans out for you and fingers crossed one of the 3 timetables work out, I can only imagine how stressfull and challenging it all is for you!

  26. #26
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Shade Cells in Staff Rota if Staff Are at Work

    I have had a play with your latest workbook and master rota, which needs a couple more columns for the task and day.

    H4 contains a drop-down - change the day to Monday to see the rota change based on the data in the master table.

    The three CF rules are:

    =SUMPRODUCT(($A$3:$A$13=$H6)*($B$3:$B$13=$I$2)*($C$3:$C$13="A")*($D$3:$D$13=$H$4)*($E$3:$E$13<=I$4)*($F$3:$F$13>=I$4+((1/24/60)*15)))=1
    =SUMPRODUCT(($A$3:$A$13=$H6)*($B$3:$B$13=$I$2)*($C$3:$C$13="B")*($D$3:$D$13=$H$4)*($E$3:$E$13<=I$4)*($F$3:$F$13>=I$4+((1/24/60)*15)))=1
    =SUMPRODUCT(($A$3:$A$13=$H6)*($B$3:$B$13=$I$2)*($C$3:$C$13="C")*($D$3:$D$13=$H$4)*($E$3:$E$13<=I$4)*($F$3:$F$13>=I$4+((1/24/60)*15)))=1

    You will see that I have added a row of timeslots above the table - this needs to stay, but can be hidden by making the text white, if you wish.

    I hope this gets you started.
    Attached Files Attached Files

  27. #27
    Registered User
    Join Date
    08-04-2020
    Location
    London
    MS-Off Ver
    2019
    Posts
    25

    Re: Shade Cells in Staff Rota if Staff Are at Work

    Hi Ali, thank you so much! I really appreciate this! This really has given me an amazing start thank you! I will build on this from here - really appreciate your help thanks again

  28. #28
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Shade Cells in Staff Rota if Staff Are at Work

    My pleasure. Let us know how you get on.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

  29. #29
    Registered User
    Join Date
    08-04-2020
    Location
    London
    MS-Off Ver
    2019
    Posts
    25

    Re: Shade Cells in Staff Rota if Staff Are at Work

    Hi Ali, what you have worked on here is truly amazing I honnestly cant thank you enough I have had a play with it and it fab! Thank you!

  30. #30
    Registered User
    Join Date
    08-04-2020
    Location
    London
    MS-Off Ver
    2019
    Posts
    25

    Re: Shade Cells in Staff Rota if Staff Are at Work

    Hi Guys

    I just need a little mre help on this Spreadsheet if thats ok I do apologise for being such an Excel newbie! But its great that there are forums welcoming people of all skill levels like this and supporting.

    AliGW has been amazing in starting me off on this and following Ali's help I was able to add a CF rule to shade a 4th colour that I needed I have also hidden some cells etc to make the Spreadsheet look how I need it and with completing those tasks I am pleased with myself... this is encouraging me to learn more about Excel and I think I'm going to start on the basics and work up and not jump into an advanced task like this...7

    Anyway, the help I need is as follows - I have attached the latest version of the Spreadsheet and you will see the staff rota and to the side of it the staff hours in days the first day in cells CI-CN is the ones setup by Ali - I then tried to to the same and have for each day of the week but no matter what I try and I have tried adding extra CF - but not knowing exactly what I am doing and I have also tried to copy over the formatting but that didnt seem to work either... You will notice in the cells setup by Ali CI-CN if you change Sunday to any other day and then from the day drop down on the rota it works... so I think I am right in saying I need to somehow copy over the formatting to the other days?

    I hope all this makes sense from a novice user

    Thanks in advance
    Freddy
    Attached Files Attached Files

  31. #31
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,427

    Re: Shade Cells in Staff Rota if Staff Are at Work

    I suggest that the assignments be made in a proper row over row table as modeled on the Assignment sheet.
    The following formula is then used to populate Week, Task, Start and End:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    There will be no need to change the conditional formatting formulas.
    Note that the day of the week will be conditional on the value in cell H4 on the Rota sheet.
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  32. #32
    Registered User
    Join Date
    08-04-2020
    Location
    London
    MS-Off Ver
    2019
    Posts
    25

    Re: Shade Cells in Staff Rota if Staff Are at Work

    Good Moning JeteMc thank you for helping with my request... I have looked and played with the ammended spreadsheet I'm not sure if I'm not fully understanding so I'm sorry if I'm missing something... I can see now that when I change the day on the table it changes the slection in the table showing what hours that staff are doing which is great. What I am confused over and this maybe a newbie thing so apologies if this is something that may seem obvious, I cant see how I can create a schedule for the week that then pulls into the rota table? If that makes sense? So, what I was thinking initially was to have a table for each day with the staff hours that pulled into the main table? I hope this makes sense?

    Thank you!

  33. #33
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,427

    Re: Shade Cells in Staff Rota if Staff Are at Work

    Which is the "rota table"? Is it the conditionally highlighted range in columns H:GC or the values displayed in column CI:ED?
    My thinking is that the weekly schedule would be placed on the assignments sheet in row over row arrangement which is (IMO) the best way to enter data in Excel.
    The table on the assignment sheet can be expanded, if that is the issue, by selecting cell F79 and pressing the Tab key.
    I feel that we may need more explanation or an example of how the weekly schedules are produced so that we can be more helpful.
    Let us know if you have any questions.

  34. #34
    Registered User
    Join Date
    08-04-2020
    Location
    London
    MS-Off Ver
    2019
    Posts
    25

    Re: Shade Cells in Staff Rota if Staff Are at Work

    Hi JeteMc, firstly my apologies it seems that when I tried this today I dont know what happend but it seems there was a glitch on my system as I am just trying it again now I have more time and its working amazing! I can't thank you enough!!! Its doing exactly what I want it to do, so when I change the data in the assignements sheet its shading the main rota

    Is it possible in anyway that I could expand the table in the assignments sheet so that I can enter a Week B and have the tabe on Rota sheet show the Week B schedule if that makes sense? I know how to expand the table as you have kindly already told me that bit its just how to change the rota on the rota sheet to Week B and show that data?

    The last thing I would like to ask advice on is if more staff are added would it be easy for me to add them? This isnt an issue at the moment I'm just thinking later on... as this could be a possibility...

    Thank you so much for your help

    Freddy

  35. #35
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,427

    Re: Shade Cells in Staff Rota if Staff Are at Work

    Lets try the following as to adding a Week B
    On the Rota sheet:
    1. Cell I2 is populated using: =RIGHT(H3,1)
    Note to change the week change cell H3, I'd suggest using data validation if practical.
    2. Cells in column CJ are populated using: =I$2
    3. Cells in column CK and CM:CN are populated using:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.
    Attached Files Attached Files

  36. #36
    Registered User
    Join Date
    08-04-2020
    Location
    London
    MS-Off Ver
    2019
    Posts
    25

    Re: Shade Cells in Staff Rota if Staff Are at Work

    Hi JeteMc, I think we are about there on this and I am so pleased with the help and support from you guys on this forum! I will need to change the staff names from Staff 01-11 to the actual staff names when I change Staff 01 to Freddy for example on the Assignments sheet and on the rota sheet the data then doesnt filter accross and clears the whole table... any ideas? I thought if the cells matched it would work? Obviously it doesnt sorry it's probably something simple?...

    Thank you

  37. #37
    Registered User
    Join Date
    08-04-2020
    Location
    London
    MS-Off Ver
    2019
    Posts
    25

    Re: Shade Cells in Staff Rota if Staff Are at Work

    Hi JeteMc, I have been having another play with the Spreadsheet and learning by trial and error as I go along

    I think I'm doing well and the question now really that I have is just that when I try and expand the assignments by selecting cell F79 and pressing the Tab key it adds an extra line but whatever data I put in the extended lines it doesnt feed through to the main shaded rota, I am obviously missing something? I wanted to add a couple of blank lines and then add in a week b but in those cells the data doesnt feed through if that makes sense? If I can get those cells to work everything is sorted I need to bring the cells in assignments down to row 160 if you could help, I've tried but seem to fail...

    Thank you

  38. #38
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,427

    Re: Shade Cells in Staff Rota if Staff Are at Work

    I appended the table on the Assignment sheet through row 160 and made a modification to the formula that populates columns CK and CM:CN
    CK:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    CM:CN:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.
    Attached Files Attached Files

  39. #39
    Registered User
    Join Date
    08-04-2020
    Location
    London
    MS-Off Ver
    2019
    Posts
    25

    Re: Shade Cells in Staff Rota if Staff Are at Work

    Hi JeteMc, this is absolutely amazing and now works exactly how I wanted I am so pleased with it! There was one thing I totally forgot and if its too much trouble or a problem its fine I can make do without. I just didnt factor in for lunch breaks... I have attached a pic of the assignments sheet to show what I am thinking and if its possible it would be great but if not I can do without. My thinking is that if we had a lunch start and lunch end column could it be possible to have that shade red on the main rota? Obviously the shading will already be done by the selected task - a,b,c or m but was thinking if there is anyway that the luch start/finish could overide and shade red that would be great?

    Thank you so much and sorry for only just thinking about the lunch breaks
    Attached Images Attached Images

  40. #40
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,427

    Re: Shade Cells in Staff Rota if Staff Are at Work

    The conditional formatting rule for lunch proceeds the others, produces red fill and reads: =SUMPRODUCT(($CI$5:$CI$15=$H6)*($CL$5:$CL$15=$H$4)*($CO$5:$CO$15<=I$4)*($CP$5:$CP$15>=I$4+((1/24/60)*15)))=1
    Let us know if you have any questions.
    Attached Files Attached Files

  41. #41
    Registered User
    Join Date
    08-04-2020
    Location
    London
    MS-Off Ver
    2019
    Posts
    25

    Re: Shade Cells in Staff Rota if Staff Are at Work

    Hi JeteMc, this is absolutely incredible! I cant thank you enough! It's amazing and perfect! I can start using thi straight away

    I'm just wondering how I would add additional staff if that should happen? I'm guessing that it would change all of the formulas? and maybe the CF? Is there a quick way that wouldnt take up too much of your time that you can make it so it would support 20 staff this will be more than ever needed but I'm thinking if I have a template ready I can just ammednt if necessary by deleting unused rows to leave the correct amount of current staff if that makes sense? I guess it would mean adding more rows on the rota sheet and quite a lot on the assignment sheet? I have tried but it didnt work for me so I guess itis because of the formulas? No probs if this isn't possible. What you have done is incredible and I cant wait to use it.

  42. #42
    Registered User
    Join Date
    08-04-2020
    Location
    London
    MS-Off Ver
    2019
    Posts
    25

    Re: Shade Cells in Staff Rota if Staff Are at Work

    Hi JeteMc in addition to my earlier message I have been playing around trying to get it working but still struggling sorry, I just thought I would add one more CF like I did before and this one to highlight when staff are not in I did what I did before based on AliGW's instruction but when I create the CF and add it in it just doesnt shade anything... I promise this is the last thing I can think of for this Spreadsheet... its no big issue if I can't have that it was again just an after thought... I'm so pleased with what you guys on here have helped me achieve

  43. #43
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,427

    Re: Shade Cells in Staff Rota if Staff Are at Work

    The Rota and Assignment sheets are now set to accommodate 20 people for weeks A and B.
    I suggest hiding unused rows rather than deleting.
    Not sure that I understand about highlighting staff that are not in. Does that mean that if there is no Start nor End time for a staff member that the cells in columns AK:GF of that row should highlight a certain color (see Staff 02)? If so then the conditional formatting formula could be:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.
    Attached Files Attached Files

  44. #44
    Registered User
    Join Date
    08-04-2020
    Location
    London
    MS-Off Ver
    2019
    Posts
    25

    Re: Shade Cells in Staff Rota if Staff Are at Work

    Hi JeteMc, you are an absolute genius! Its all working amazing! I cant thank you enough and AliGW for getting me started on this. Shall I mark this thread as solved? Thanks again and I hope you have a lovely weekend ahead!

  45. #45
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,427

    Re: Shade Cells in Staff Rota if Staff Are at Work

    You're Welcome and thank you for the feedback. Yes, please mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.

  46. #46
    Registered User
    Join Date
    08-04-2020
    Location
    London
    MS-Off Ver
    2019
    Posts
    25

    Re: Shade Cells in Staff Rota if Staff Are at Work

    All soved and sorted! A big thank you to AliGW for getting me started and a massive thank you for all the extra work by JeteMc you have been incredible! Thanks guys can't express my appreciation enough!!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Looking for a spreadsheet for Excel Formulas & Functions.
    By Boopster in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-06-2018, 01:50 AM
  2. How are the formulas connected in excel spreadsheet
    By jim1234 in forum Excel General
    Replies: 11
    Last Post: 08-13-2016, 10:01 PM
  3. Complex formulas in my excel spreadsheet
    By lucascain in forum Excel General
    Replies: 7
    Last Post: 04-27-2015, 12:23 PM
  4. Need formulas for stock transaction data
    By Par in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-02-2014, 02:29 PM
  5. [SOLVED] Formulas with Names in another Excel Spreadsheet
    By trizzo in forum Excel General
    Replies: 6
    Last Post: 04-11-2012, 11:19 PM
  6. Excel 2007 : Excel spreadsheet troubleshoot formulas
    By c4rolo in forum Excel General
    Replies: 2
    Last Post: 01-23-2010, 02:35 PM
  7. [SOLVED] hOW DO i COPY FORMULAS FROM ONE SPREADSHEET TO ANOTHER IN EXCEL
    By Isabel in forum Excel General
    Replies: 1
    Last Post: 05-01-2006, 01:25 PM

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