Multiple criteria in conditional formatting

[katel99]

Hello

I am using excel to demonstrate supply Vs demand, with regards to project resource capacity. What I am trying to achieve (using conditional formatting), as a look up between two sheets, that identifies IF (name) in Sheet 1, has a capacity less than usual in Sheet 2, then highlight the relevant cell for the week in Sheet 1.

Sheet 1 will be where the user enters their demand (i.e. 3 days on the Alpha project, 2 days on Beta). I would like cells in Sheet 1 to highlight in red if the Sheet 2 shows that person has less than their full capacity. I have highlighted in orange the cells that show they don't not have full capacity for Sheet 2, and have written RED in the cells I would expect to this to be reflected.

Sheet 2 (Supply)

Resource Type	Resource Name	Working Days	23/07/2018	30/07/2018	06/08/2018	13/08/2018	20/08/2018	27/08/2018
PM	Joe	5	5	5	5	4	0	5
PM	Carol	5	5	5	5	5	5	5
SA	Ian	5	0	0	5	5	5	5
BA	Sue	3	3	3	0	3	3	3

<tbody>
</tbody>

Sheet 1 (Demand);

Project	Resource Type	Resource Name	23/07/2018	30/07/2018	06/08/2018	13/08/2018	20/08/2018	27/08/2018
Alpha	PM	Joe				RED	RED
Beta	PM	Carol
Gamma	SA	Ian	RED	RED
Beta	BA	Sue			RED
Gamma	PM	Joe				RED	RED

<tbody>
</tbody>

Any help would be appreciated. As you can see, there are multiple variables. Ultimately, I want to combine lookups.

I am working from a legacy document, and am not able to change, so amending the layout of the sheets may not be possible.

Thanks in advance,

Kate

 

[Special-K99]

Where is "full capacity" ? That term doesnt appear anywhere in your diagrams.

Is this "Working Days" or is it somewhere else ?

To get Excel to decide if someone is working less than full capacity you have to define "full capacity".

So where is each person's "full capacity" ?

 

[katel99]

Where is "full capacity" ? That term doesnt appear anywhere in your diagrams.

Is this "Working Days" or is it somewhere else ?

To get Excel to decide if someone is working less than full capacity you have to define "full capacity".

So where is each person's "full capacity" ?

Hello!

Yes, sorry! Each persons full capacity is the “working days” column.

Thanks,

Kate