Hi all,
I'm new to excel (and spreadsheets in general) and am struggling to fully grasp all the functions available to me, so it is I find myself begging those with better understandings than myself for tips & pointers.
What I have done is created a rota for my boss. So far I have got it so once you type in the month, it places a line of days (correct) to the number of days in that month.
Then, in some of the cells it will check to see what day it falls on. For example, I always work Sunday & Monday night shifts, so I have mine set to check for "Sun" or "Mon" and then put an 'X' on those days for my line and a blank space for any other days. There's another guy and a woman who do the remaining nights, and I've set it check "Tue", "Wed" and "Thu" for hers and "Fri" and "Sat" for his - again, putting an 'X' on their respective days or a blank space on the others.
So far - so good.
However, I've left many areas blank at this time as they wlil be left for the boss to fill in according to who's on annual leave, called in sick, preferences for mornings or afternoons and so on.
At the bottom of the page, I'm trying to run a check - where it uses a SUM like function to confirm that there are 2xmornings, 2xafternoons, 1xnight (automated) and finally 1xsleeper.
I've run dummy tests by putting 'S' (sleeper) into a specific cell and using the following:
I've also tried:
The idea I had, was that it would check the range from D6 through to D17 (our staff list) and look for the letter 'S' and report whether the boss had forgotten to place a sleeper on shift (I'll be replicating this code, once working, to check for 2xMornings, etc.etc.)
The above codes do not seem to function though.
Most commonly, we put staff on a 24hr, so they'll frequently have an 'AS' (afternoon + sleep) shifts, so it needs to check whether 'S' is part or whole of the cell.
Can anyone assist please?
I'm new to excel (and spreadsheets in general) and am struggling to fully grasp all the functions available to me, so it is I find myself begging those with better understandings than myself for tips & pointers.
What I have done is created a rota for my boss. So far I have got it so once you type in the month, it places a line of days (correct) to the number of days in that month.
Then, in some of the cells it will check to see what day it falls on. For example, I always work Sunday & Monday night shifts, so I have mine set to check for "Sun" or "Mon" and then put an 'X' on those days for my line and a blank space for any other days. There's another guy and a woman who do the remaining nights, and I've set it check "Tue", "Wed" and "Thu" for hers and "Fri" and "Sat" for his - again, putting an 'X' on their respective days or a blank space on the others.
So far - so good.
However, I've left many areas blank at this time as they wlil be left for the boss to fill in according to who's on annual leave, called in sick, preferences for mornings or afternoons and so on.
At the bottom of the page, I'm trying to run a check - where it uses a SUM like function to confirm that there are 2xmornings, 2xafternoons, 1xnight (automated) and finally 1xsleeper.
I've run dummy tests by putting 'S' (sleeper) into a specific cell and using the following:
Code:
=IF(COUNTIF(D6:D17,"*S*"),"OK","No Sleeper")
Code:
=IF(ISERR(FIND("S",D6:D17)),"No Sleeper","OK")The idea I had, was that it would check the range from D6 through to D17 (our staff list) and look for the letter 'S' and report whether the boss had forgotten to place a sleeper on shift (I'll be replicating this code, once working, to check for 2xMornings, etc.etc.)
The above codes do not seem to function though.
Most commonly, we put staff on a 24hr, so they'll frequently have an 'AS' (afternoon + sleep) shifts, so it needs to check whether 'S' is part or whole of the cell.
Can anyone assist please?
