conditional formatting

[QuizToon]

Hi all,

I am trying to make a cell highlight in red if there are more than 1 condition present. The table below represents a work rota and also a holiday planner underneath. The initials of each employee are entered into the rota for each day they are at work. If they are on a rest day (RD) or have booked holidays (H) these are entered into the planer at the bottom. The holiday planner can be populated for the whole year in some cases whereas the rota is done around 2 weeks in advance.

With 100 employees and 25 sites to work with this can be quite a difficult task and time consuming task.

To highlight any errors would make the task much quicker to do.

Is it possible to have some conditional formatting to the rota part of the sheet.

The sequence would be - If the initials are input into the rota then a check would be made to see if the that person has either rest days or holiday booked and if so highlight the cell in a colour to highlight that fact. Each employees given initials are in the bottom part of the planner to match 2. In the instance of BF/OG, then there are 2 people working that site on that day.

I hope that is all making sense, its had my head in a spin for ages. I think I did manage to get it working for single cells and I had the conditional formatting in the planner part which was wrong.

I cant work out how to check the initials in the rota against those in the planner column and see if there is 'H' or 'RD' in the planner. The highlight needs to be in the rota part.

eg The cells with JS in the 10th and 11th against site 1, should highlight to show that JS has booked holiday those days and should not be included in the rota.

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD="align: center"]DATE
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]10TH
[/TD]
[TD="align: center"]11TH
[/TD]
[/TR]
[TR]
[TD="align: center"]DAY
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]SITE 1
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]JS
[/TD]
[TD="align: center"]JS
[/TD]
[/TR]
[TR]
[TD="align: center"]SITE 2
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]GP
[/TD]
[TD="align: center"]GP
[/TD]
[/TR]
[TR]
[TD="align: center"]SITE3
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]BF/OG
[/TD]
[TD="align: center"]BF/OG
[/TD]
[/TR]
[TR]
[TD="align: center"]SITE4
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]TR
[/TD]
[TD="align: center"]TR
[/TD]
[/TR]
[TR]
[TD="align: center"]SITE 5
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]DB
[/TD]
[TD="align: center"]DB
[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]PLANNER
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]EMPLOYEE
[/TD]
[TD="align: center"]INITS
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]1
[/TD]
[TD="align: center"]JS
[/TD]
[TD="align: center"]H
[/TD]
[TD="align: center"]H
[/TD]
[/TR]
[TR]
[TD="align: center"]2
[/TD]
[TD="align: center"]GP
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]H
[/TD]
[/TR]
[TR]
[TD="align: center"]3
[/TD]
[TD="align: center"]BF
[/TD]
[TD="align: center"]RD
[/TD]
[TD="align: center"]RD
[/TD]
[/TR]
[TR]
[TD="align: center"]4
[/TD]
[TD="align: center"]OG
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]5
[/TD]
[TD="align: center"]TR
[/TD]
[TD="align: center"]H
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]6
[/TD]
[TD="align: center"]DB
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Many Thanks

 

[TheSpiderman]

I don't know any way to make it work with the sort order you have in your planner, except passing by Workbook_SheetChange and make some coding

But... if you can change your planner section to be sorted by INITS instead of EMPLOYEE you can use this :

Add a conditional formatting

Formula:
=IF(LEN(C5)>2,OR(VLOOKUP(C5,$B$13:$D$18,COLUMN(C5)-COLUMN($B$13)+1)<>"",VLOOKUP(RIGHT(C5,2),$B$13:$D$18,COLUMN(C5)-COLUMN($B$13)+1)<>""),VLOOKUP(C5,$B$13:$D$18,COLUMN(C5)-COLUMN($B$13)+1)<>"")
Notes, addresses are for this example, if you need to make changes
C5 is the first (top-left) cell of your ROTA INITS data (important : do not put $ sign to the new value)
$D$13 is the first (top) cell of your PLANNER INITS data
$D$18 is the last (bottom) cell of your PLANNER INITS data

Whatever formatting you want

Apply to range:
=$C$5:$D$9

Result :

Excel 2010
	A	B	C	D
1
2	DATE		10TH	11TH
3	DAY
4
5	SITE 1		JS	JS
6	SITE 2		GP	GP
7	SITE3		BF/OG	BF/OG
8	SITE4		TR	TR
9	SITE 5		DB	DB
10
11		PLANNER
12	EMPLOYEE	INITS
13	3	BF	RD	RD
14	6	DB
15	2	GP		H
16	1	JS	H	H
17	4	OG
18	5	TR	H
Feuil1

Have a nice day!
Den

 

[Gerald Higgins]

So if I'm reading this correctly, you want to take the name (initials) from the first table, and look it up in the second table for that date, and IF it has an H for that date, make the cell in the first table red, yes ?

If so, that's do-able fairly readily.

One thing that will make it MUCH harder is if you have multiple sets of initials in the same cell, such as "BF/OG".
That's still do-able but it's MUCH harder and I strongly recommend you don't go down that road, and instead focus on having one single name in each cell, and never more than one.

ALSO, consider what to do if you have two or more people with the same initials - John Doe and Jane Doe, and so on.
Consider listing their full name, or maybe even better, consider using unique employee reference numbers.

But as long as there are no shared initials, this can be done.

Let's make some assumptions about where all this data is.
In fact, let's assume that the data is in exactly the same cells as in TheSpiderman's post.
Select cell C5, apply a new condition, and select the option to Use a formula to determine which cells to format.

Here's the formula
=VLOOKUP(C5,$B$13:$D$18,COLUMN()-1,FALSE)="H"

and apply this to the range C5:D9.

The COLUMN()-1 bit is important to work out which column to refer to in the VLOOKUP formula - you will probably need to adjust this if your data is NOT laid out as shown.

 

[TheSpiderman]

...Notes, addresses are for this example, if you need to make changes
C5 is the first (top-left) cell of your ROTA INITS data (important : do not put $ sign to the new value)
$D$13 is the first (top) cell of your PLANNER INITS data
$D$18 is the last (bottom) cell of your PLANNER INITS data
...

Should be
Notes, addresses are for this example, if you need to make changes
C5 is the first (top-left) cell of your ROTA INITS data (important : do not put $ sign to the new value)
$B$13 is the first (top) cell of your PLANNER INITS data
$D$18 is the last (bottom-right) cell of your PLANNER INITS data

Didn't find a way to edit my original post

 

[QuizToon]

Hi Gerald,

Thanks for the reply. There has to be 2 sets of initials on some occasions as there will be 2 people in the site. Initials are always unique.

Going to play with yor suggestion too. See if I can understand what you have done and see if I get my head round it.

Thanks again

 

[QuizToon]

HI,

Thanks for this - going to have a bash at it and see if it does the thing for me.

Thanks

 

[QuizToon]

Hi Spiderman,

I have this working as you said, thanks for that. I sorted by initials.

If I have the planner set out for the year will I just change the range in the formula to suit?

eg: Assuming the whole planner/rota runs right up to the end of the year and finishes on column NI45 or will this only work for the 2 columns. Don't want to try it in case I break it

=IF(LEN(C5)>2,OR(VLOOKUP(C5,$B$13:$NI$45,COLUMN(C5)-COLUMN($B$13)+1)<>"",VLOOKUP(RIGHT(C5,2),$B$13:$NI$45,COLUMN(C5)-COLUMN($B$13)+1)<>""),VLOOKUP(C5,$B$13:$NI$45,COLUMN(C5)-COLUMN($B$13)+1)<>"")

My rota and planner runs from I8 to NI18, the holiday part runs from I21 to NI45

the column for the initials is g21 to G45.

I really do appreciate the assistance

 

[markmzz]

My rota and planner runs from I8 to NI18, the holiday part runs from I21 to NI45

the column for the initials is g21 to G45.

Hi QuizToon,

Try this in I8 Conditional Formatting:

=ISNUMBER(FIND(" "&VLOOKUP(LEFT(I8,2),$G$21:$NI$45,COLUMNS($I8:I8)+2,0)&" "," H RD "))+
ISNUMBER(FIND(" "&VLOOKUP(RIGHT(I8,2),$G$21:$NI$45,COLUMNS($I8:I8)+2,0)&" "," H RD "))


[TABLE="class: grid, width: 413"]
<tbody>[TR]
[TD][/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD]DATE[/TD]
[TD][/TD]
[TD][/TD]
[TD]10TH[/TD]
[TD]11TH[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD]DAY[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD]SITE 1[/TD]
[TD][/TD]
[TD][/TD]
[TD]JS[/TD]
[TD]JS[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD]SITE 2[/TD]
[TD][/TD]
[TD][/TD]
[TD]GP[/TD]
[TD]GP[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][/TD]
[TD]SITE3[/TD]
[TD][/TD]
[TD][/TD]
[TD]BF/OG[/TD]
[TD]BF/OG[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD][/TD]
[TD]SITE4[/TD]
[TD][/TD]
[TD][/TD]
[TD]TR[/TD]
[TD]TR[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD][/TD]
[TD]SITE 5[/TD]
[TD][/TD]
[TD][/TD]
[TD]DB[/TD]
[TD]DB[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD][/TD]
[TD][/TD]
[TD]PLANNER[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD][/TD]
[TD]EMPLOYEE[/TD]
[TD]INITS[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD]JS[/TD]
[TD][/TD]
[TD]H[/TD]
[TD]H[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]22[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD]GP[/TD]
[TD][/TD]
[TD][/TD]
[TD]H[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]23[/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD]BF[/TD]
[TD][/TD]
[TD]RD[/TD]
[TD]RD[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]24[/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[TD]OG[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]25[/TD]
[TD][/TD]
[TD="align: right"]5[/TD]
[TD]TR[/TD]
[TD][/TD]
[TD]H[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]26[/TD]
[TD][/TD]
[TD="align: right"]6[/TD]
[TD]DB[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]27[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]***[/TD]
[TD]**[/TD]
[TD]**********[/TD]
[TD]*********[/TD]
[TD]**[/TD]
[TD]*******[/TD]
[TD]*******[/TD]
[TD]**[/TD]
[TD]**[/TD]
[TD]**[/TD]
[TD]**[/TD]
[/TR]
</tbody>[/TABLE]


Markmzz

 

[TheSpiderman]

...
If I have the planner set out for the year will I just change the range in the formula to suit? Yes

eg: Assuming the whole planner/rota runs right up to the end of the year and finishes on column NI45 or will this only work for the 2 columns. Don't want to try it in case I break it

=IF(LEN(C5)>2,OR(VLOOKUP(C5,$B$13:$NI$45,COLUMN(C5)-COLUMN($B$13)+1)<>"",VLOOKUP(RIGHT(C5,2),$B$13:$NI$45,COLUMN(C5)-COLUMN($B$13)+1)<>""),VLOOKUP(C5,$B$13:$NI$45,COLUMN(C5)-COLUMN($B$13)+1)<>"")

My rota and planner runs from I8 to NI18, the holiday part runs from I21 to NI45

the column for the initials is g21 to G45.

I really do appreciate the assistance

Based on what you said you have to change also C5 for I8 and $B$13 for $G$21 like this :

=IF(LEN(I8)>2,OR(VLOOKUP(I8,$G$21:$NI$45,COLUMN(I8)-COLUMN($G$21)+1)<>"",VLOOKUP(RIGHT(I8,2),$G$21:$NI$45,COLUMN(I8)-COLUMN($G$21)+1)<>""),VLOOKUP(I8,$G$21:$NI$45,COLUMN(I8)-COLUMN($G$21)+1)<>"")

and apply to range:
=$I$8:$NI$18

Have fun!
Den