Formatting words to change colours

[dandavis1]

Please see attached doc.

Sheet one shows 3 tables with percentage figures. Group A, Group B and Group C. Each figure is shown red or green. On sheet 2 is a smaller table showing the month of January. I need the names on sheet 2 to match the cells from the other table on sheet 1. For example in Group A, Dan, Dean and Matt should be shown as Red on sheet 2 and Rich should be in green.

Hope this makes sense.

Thanks alot!!

[NBVC]

See attached..

You need to use Named ranges if you are referencing other sheets within Conditional Formatting...

So I named each group in Sheet1, GroupA, GroupB and GroupC

Then I named each respective Target Percentage: GroupATarget, GroupBTarget and GroupCTarget

Then applied conditional Formatting to GroupA names in Sheet2:

=INDEX(GroupA,MATCH(D7,OFFSET(GroupA,0,MONTH($C$5&" 1, 2010")-1,,1),0),2)<GroupATarget


and

=INDEX(GroupA,MATCH(D7,OFFSET(GroupA,0,MONTH($C$5&" 1, 2010")-1,,1),0),2)>=GroupATarget

and similarly for other 2 groups replaceing GroupA for GroupB and GroupC as appropriate in both formulas.

[dandavis1]

Thanks for your time on this, much apreciated. The names on Sheet 2 however dont appear to be changing colour. Any suggestions?

[dandavis1]

Having thought about it, i think im trying to be too complicated, see attached new version.

Can i conditional format the cells with the names in column B to be shown in either red or green text?

The conditional format I have tried and failed with is =IF(B6="Y") then i have formatted the text as green...............=IF(B6="N") then i have formatted the text red.

It is coming up with an error.

Thanks,

[NBVC]

Are you sure about the previous one not working.. I just tried a couple and still worked....

Anyways, the new sheet, the formulas should be =C6="Y" and =C6="N"