Excel Formula Help For Shift Pattern Indicator Table Please :O)

[Helen]

Hello Excel Helpers

I would be very grateful if you could give me, or help me to get a
formula to highlight if an individual member of staff works/is
rostered for 10(or more than 10) shifts in a row that will give the
effect of either

a. Highlight in colour a name in Column A or
b. Enter the amount of days in Column B (ie 10 12 13) or
c. Both!

I've been reading for over two hours and can't find a relevant thread,
although I'm sure this one has been asked before.

Here is what the first part of the table looks like (I actually have
the whole date in the cells for ROW2 but they're not relevant)

A B C D E F G H I J K L M N O P Q R S T U V W X Y Z aa
1 Week No: 26 27 28 29
2 Day&Date: s s m t w t f s s m t w t f s s m t w t f s s m t w
3 Bert 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1
4 John 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1
5 Fred 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1
6 Joan 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1
7 Igor 1 1 1
8 Gherkin 1 1 1 1 1 1 1 1 1 1 1

and so on. The complete spreadsheet cell range is from A1 to GP100

As I have only just started this spreadsheet, the values *1* can
always be substituted for something else if it would make life easier.

I await the replies with eager ANTICIPATION!

I'll put the kettle on if anyone want a drink.

Helen )

 

[David McRitchie]

Hi Helen,
You want to use Conditional Formatting
http://www.mvps.org/dmcritchie/excel/condfmt.htm

Not quite the result you asked for but this would show
up those that had 10 or more days in a row, and give
an indication of those how many extra days they had
with those 10 days. Good if all of the columns are view.

So it report day 1 is in column B and the last report day is column AC
then select columns B:AC with cell B1 as the active cell
Format, Conditional Formatting,
Formula is: =COUNTIF(B1:K1,1)=10
click on the format button, choose pattern, and a pastel pattern color

You might want to make sure you do not have extraneous spaces
or other characters in your data by using the TRIMALL macro in
http://www.mvps.org/dmcritchie/excel/join.htm#trimall

 

[Jason Morin]

You could insert a row beneath each name. In the first
empty row (in B4), insert this and fill across to GP4:

=IF(B3="",0,B3+A4)

Copy the row and paste in the empty rows below. Then use:

=MAX(B4:GP4)

to bring in the max. # of day worked in row for each name.

HTH
Jason
Atlanta, GA

 

[Helen]

Jason & David

Thank you both for your information. It was very helpful :O)

I put the formulas =IF(C3="",0,C3+B103) further down the spreadsheet,
so that I didn't have to enter extra rows. Inserting =MAX(C103:GP103)
into Column B. Then I used Condition Formatting (which is new to
me)to highlight results equal to or greater than 10, and to not show
any values for under 10. And also to highlight todays date making it
easier for the user to find the starting point to work from! Top
stuff!

It looks great.

However I didnt get to grips with Davids...

"Not quite the result you asked for but this would show
up those that had 10 or more days in a row, and give
an indication of those how many extra days they had
with those 10 days. Good if all of the columns are view.
So it report day 1 is in column B and the last report day is
column AC then select columns B:AC with cell B1 as the active cell
Format, Conditional Formatting,
Formula is: =COUNTIF(B1:K1,1)=10
click on the format button, choose pattern, and a pastel pattern
color

The actual full data entry area runs from C3 to GP100 covering weeks
26 through to week 52.

I did try it on the sample I provided here but it just seemed to
highlight randomly two four or one cell, sometimes it highlighted a
cell with no input.
It looked pretty but not what I was expecting!

If I could have the shift pattern for each person that is equal to or
greater than 10 to be highlighted it would be a perfect spreadsheet.

Could you possibly give me a little more help?

This is what it looks like now!

A B C D E F G H I J K L M N O P Q R S T U V W X Y Z
aa
1 Week No: 26 27 28 29
2 Day&Date: s s m t w t f s s m t w t f s s m t w t f s s m t
3 Bert =MAX(C103:GP103)1 1 1 1 1 1 1 1 1 1 1 1 1 1 1
4 John =MAX 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1
5 Fred =MAX 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1
6 Joan =MAX 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1
7 Igor =MAX 1 1 1
8 Gherkin =MAX 1 1 1 1 1 1 1 1 1 1 1

- -- - - - - - - - - - - - - - - -- - - - - - - - - -
further down the sheet...
A B C D
103 =IF(C3="",0,C3+B103) =IF(D3="",0,D3+C103)
104 =IF(C4="",0,C4+B104) =IF(D4="",0,D4+C104)
105
106
107
108


Helen