Checking values in a range - reporting error if not?

Karlm

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

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Karlm

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

Code:
=IF(COUNTIF(F6:F17,"S")OR(COUNTIF(F6:F17,"AS"))>0,"OK","##")
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,703
Messages
5,833,218
Members
430,197
Latest member
edeibold

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top