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)

[TABLE="width: 875"]
<tbody>[TR]
[TD="align: center"]Resource Type[/TD]
[TD="align: center"]Resource Name[/TD]
[TD="align: center"]Working Days[/TD]
[TD="align: center"]23/07/2018 [/TD]
[TD="align: center"]30/07/2018 [/TD]
[TD="align: center"]06/08/2018[/TD]
[TD="align: center"] 13/08/2018 [/TD]
[TD="align: center"]20/08/2018[/TD]
[TD="align: center"] 27/08/2018[/TD]
[/TR]
[TR]
[TD="align: center"]PM[/TD]
[TD="align: center"]Joe[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]5[/TD]
[/TR]
[TR]
[TD="align: center"]PM[/TD]
[TD="align: center"]Carol[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]5[/TD]
[/TR]
[TR]
[TD="align: center"]SA[/TD]
[TD="align: center"]Ian[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]5[/TD]
[/TR]
[TR]
[TD="align: center"]BA[/TD]
[TD="align: center"]Sue[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]3[/TD]
[/TR]
</tbody>[/TABLE]


Sheet 1 (Demand);

[TABLE="width: 821"]
<tbody>[TR]
[TD="align: center"]Project[/TD]
[TD="align: center"]Resource Type[/TD]
[TD="align: center"]Resource Name[/TD]
[TD="align: center"]23/07/2018 [/TD]
[TD="align: center"]30/07/2018[/TD]
[TD="align: center"] 06/08/2018[/TD]
[TD="align: center"] 13/08/2018 [/TD]
[TD="align: center"]20/08/2018[/TD]
[TD="align: center"] 27/08/2018[/TD]
[/TR]
[TR]
[TD="align: center"]Alpha[/TD]
[TD="align: center"]PM[/TD]
[TD="align: center"]Joe[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"] RED[/TD]
[TD="align: center"] RED[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]Beta[/TD]
[TD="align: center"]PM[/TD]
[TD="align: center"]Carol[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]Gamma[/TD]
[TD="align: center"]SA[/TD]
[TD="align: center"]Ian[/TD]
[TD="align: center"] RED[/TD]
[TD="align: center"]RED[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]Beta[/TD]
[TD="align: center"]BA[/TD]
[TD="align: center"]Sue[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"] RED[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]Gamma[/TD]
[TD="align: center"]PM[/TD]
[TD="align: center"]Joe[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]RED[/TD]
[TD="align: center"] RED[/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]


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