Checking values in a range - reporting error if not?


New Member
Mar 18, 2011
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:
=IF(COUNTIF(D6:D17,"*S*"),"OK","No Sleeper")
I've also tried:
=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?

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.


New Member
Mar 18, 2011
Sods law, no sooner had I posted my request than I found the answer...

Works a treat! :)

So my next question...

Can I use an instruction of some kind to see how many times a shift has been assigned in one day?

e.g. We need only 2 members of staff on at a time, on occasion, the boss has been known to put 3 on a morning (or similar).

I need it to check there are 2 M's in a range, 2 A's in the same range and so on.

Watch MrExcel Video

Forum statistics

Latest member

This Week's Hot Topics