Conditional formatting on annual leave sheet clashing leave

Owain_b · Feb 6, 2019

Hi All,

I'm having an issue with the best way to solve a request.

We have multiple projects each with a project manger and an assistant.

On our annual leave spreadsheet we would like it to highlight if the manager and the assistant for any specific project book leave at the same time.

I was thinking of having a control sheet with a list of projects in column A and column B & C having the project manger and assistant respectively.

Can a conditional formatting formula look at the list to see if two names are on the same row in any of the rows and highlight the cells for those two people if the annual leave sheet has conflicting leave for those two people?

Or is this going to require vba to get this working.

Trying to do this with conditional formatting if possible, but don't use it very often.

Any help on where to start with this would be much appreciated.

Thanks

Chrisdontm · Feb 8, 2019

Try this;

Lets say that you put the Manager's name in cell: G2

and the Assistant's name in cell: H2

( That way you could change their name if anyone gets promoted out of their position…)

To use a formula in "CONDITIONAL FORMATTING":

Highlight the cells which you wish to be effected by the "Conditional Formatting" rule.

(In this example, we will highlight the range: B3:C100)

On the "Home" tab, click: "Conditional Formatting"

Near the bottom of the box click: "New Rule..."

Then click on the choice of:

" Use a formula to determine which cells to format "

In the formula bar at the bottom of the box that appears type in:

=AND($B3=$G$2,$C3=$H$2)

( If both of those parts are true, the cells will highlight with the colors you pick. )

Then click on the "Format" button and choose a fill color, or a font, etc...

Then click: "ok", and "ok".

Owain_b · Feb 8, 2019

Hi,

Thanks that is a really good starting point, however the names of the employees are in row 1 on the main sheet and the cells that need to be formatted are the cells for the days of the week below the names which will be filled with either 0.5 or1.0 for half day or full days. So the conditional formatting needs to match the employees in the list of employees that cannot take leave at the same time an match them to the names in row 1 on the main sheet and then check the columns for non blank cells in both columns.

Can this be done with conditional formatting? I'm getting stuck on referencing the cells that I want formatted. I was thinking of using

=AND(AND(OFFSET(B8,Row()-1,0)='Cell of first person',OFFSET(C8,Row()-1,0)='cell of second person')),AND(B8<>"",C8<>""))

The only issue is I need it to be dynamic as the B8 and C8 reference could be anything in the conditional formatting range of B8:AG372 each column with the name in row 1.

Any help would very much be appreciated
Thanks, Owain

Chrisdontm · Feb 9, 2019

I am trying to get the picture of where everything is.
So let me review what I think I understand, and you correct me where I am missing it...

All the employees are across the top in row one starting in column B all the way to column AG.
( Which would seem like you have 32 employees )

I am guessing that the dates of the year are down column A starting on row 8.
( 1/1/19, 1/2/19, 1/3/19....etc.... )

But then it looks like you are saying that the Manager and the Assistant Manager are in columns A & B.

And you don't want them both to be blank, or empty on the same row/day.
If they are both empty, then highlight both of the empty cells.

So is there different Managers and Assistant Managers listed down those columns, A & B...?

I guess I am needing to confirm where the names of the Managers are...

Also, are there two sheets involved...?
You call one the "Main Sheet"...

Owain_b · Feb 9, 2019

Hi, thanks for the reply.
Yes you have pretty much got it. There are 2 sheets. The first is laid out as you describe with dates in col A starting at row 8 and the employees in row 1.
The list of who can't have holidays at the same time is on a different sheet in cold A and B, so whoever is in col A cannot be on leave at the same time as the person in col B on the same row.
And on the main sheet if the two people have non blank cells on the same row they should be highlighted. So if they have been selected to be on leave at the same time.
Thanks

Chrisdontm · Feb 9, 2019

Ok, in order to reference the "other sheet", you have to put the name of the other sheet in the formula.

So what is the name of the other sheet, with the columns that have the Manager and the Assistant Manager listed...?

And how far down does the list of managers go down?

Another thought, are the Managers listed in column B & C on each day all the way down the Main Sheet...?

Owain_b · Feb 11, 2019

The sheet is just called 'Sheet2' and the list of managers on sheet 2 goes down to row 20 and the columns don't have headings so there is 20 combinations of managers that cannot be on leave at the same time.

On the main sheet columns B and C were just examples, the names in sheet 2 could be in any of the first row column B through to AG. And below each name in the main sheet from row 8 to row 372 there may be a cell which indicates that person is on leave normally with a 0.5 or a 1.0
But the 2 managers could be in column B and C but could be D and AG, and if they have a day if leave on the same row below their names then it needs to highlight the day of leave for both people.
Thanks

Chrisdontm · Feb 12, 2019

Lets try this;

Lets say that you put all the Manager's names in cells: A1:A20 on Sheet 2
To use a formula in "CONDITIONAL FORMATTING":
Copy this formula:

=(SUM(INDEX($B8:$AG8="",)*INDEX((IFERROR(MATCH(Main!$B$1:$AG$1,Sheet2!$A$1:$A$20,0),0)>0)*1,))>1)*(B8="")*(COUNTIF(Sheet2!$A$1:$A$20,Main!B$1)=1)*(COUNTBLANK($B8:$AG8)<32)

Highlight the cells which you wish to be effected by the "Conditional Formatting" rule.
(In this example, we will highlight the range: B8:AG372)
On the "Home" tab, click: "Conditional Formatting"

Near the bottom of the box click: "New Rule..."
Then click on the choice of:
" Use a formula to determine which cells to format "
In the formula bar at the bottom of the box that appears paste the formula that you copied.
Then click on the "Format" button and choose a fill color, or a font, etc...
Then click: "ok", and "ok".

This should work...

Conditional formatting on annual leave sheet clashing leave

Owain_b

New Member

Excel Facts

Chrisdontm

Well-known Member

Owain_b

New Member

Chrisdontm

Well-known Member

Owain_b

New Member

Chrisdontm

Well-known Member

Owain_b

New Member

Chrisdontm

Well-known Member

Similar threads

Forum statistics

Share this page

Conditional formatting on annual leave sheet clashing leave

New Member

Excel Facts

Well-known Member

New Member

Well-known Member

New Member

Well-known Member

New Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock