Hi Drumroll,
So for H4 on the Projects sheet, you want to check the hours per month on the Hour Calculations sheet and based on its value, change the colour to either green, yellow or red.
So with:
VLOOKUP($H4,'Hours Calculations'!$A$4:$B$36,2,FALSE)
we retrieve the hours per month value.
When this value is bigger then 136 you want to change the cell to red. So the first conditional formatting rule for H4 on the Projects sheet will be:
=VLOOKUP($H4,'Hours Calculations'!$A$4:$B$36,2,FALSE)>136 format cell as red.
Now do the same for the green and yellow colours.
Then drag H4 down as far as needed and click on the small square providing you with the option to "apply format only". This instead of applying format to each cell individually.
Does this yield the desired result?
Best regards,
Trowa
(sorry again i don't know how to make a table like you did)
staff hours staff hours
project 1 john 4 jill 2
project 2 greg 10 john 10
project 3 jill 3
etc.
so I have John twice and jill twice and jill should be green while john should be yellow for example
=AND(H4=$A$4,$B$4<=120) highlight green
=AND(H4=$A$4,$B$4>120,$B$4<=136) highlight yellow
=AND(H4=$A$4,$B$4>136) highlight red
The A column has all the possible names and the B column has the associated total value. H4 in this case doesn't have the dollar signs as I plan to use format painter on all the other cells once I am done with the first cell. So as I go through I will be changing the A# and B# for each person as I make more rules.
If there is another easier or faster method of doing this, please let me know, I would greatly appreciate not inputting 93 more rules