Conditional Format - using a formula

[ChrisUK]

Hi,

I have a large sheet with a selection of cells with a validation list in each cell (Yes,No,Maybe). Depending on the cell value I have a conditional format set of Green, Yellow or Red.

So far so good

However, I have inserted a new column at the start of the sheet now where the cell value is also "Yes". How can I use a formula in each of my conditional formats is do what it is now but also to fire if the cell in the new column on the same row is "Yes" - is this possible

Thank in anticipation

Chris

 

[steve the fish]

Not entirely clear what you mean? So does 'yes' have to appear in the new column for any formatting to appear?

Maybe:

=AND($A1="yes",$B1="yes")

 

[ChrisUK]

The Data table is in column AA to EZ - these cells each have a validation list which has Yes,No or Maybe and I have set a conditional format for Green, Yellow or Red based on the value in the cell

However, Now I have added a set of data in column B which is either Yes or ""

What I want to do is add a new conditional format to each of the cells in column AA to EZ to say if B[x] = "Yes" then the format should be purple

I've tried messing around with the conditional format which uses a formula and stopping when true whilst placing it at the start but nothing seems to work

Chris

 

[RoryA]

You need to use a formula condition using:
=$B2="yes"
assuming the formatting is applied from row 2. Note there is a $ before the B but there isn't one before the 2 - that is very important!

 

[steve the fish]

Its probably to do with the absolution of your cells.

These work for me in this order:

=$B1="Yes" fill purple
=AA1="Yes" fill green

etc

 

[ChrisUK]

You need to use a formula condition using:
=$B2="yes"
assuming the formatting is applied from row 2. Note there is a $ before the B but there isn't one before the 2 - that is very important!

So close!!

Problem with this is its now turning the whole row purple not just the one cell

So I have altered the formula to =AND (AA2<>"", $B2="Yes")

Works a treat now, thanks for your help and putting me on the right track

Chris