Conditional formatting combined with multiple IF statements

[Hendrik]

Hi all,

I want to select all my data (headers and values), then apply a conditional
format:

I have headers in row 1 and values in the underlying rows. When a header has
the words ''back office'' in it and the value in the cell beneath it is
greater then 0.0, then I want the cell to be filled orange.

Going on, if the header shows the word ''break'' and the value in the cell
beneath it is equal to 0.25, I want the cell to be filled yellow.

I want to do the above for a whole range of headings. I can't apply a
conditional format to each collum because it's simply too much work (BIG
file).

I'm not sure if this is possible but I appreciate all input.

Thanks!

 

[Pete_UK]

I'm a bit confused - do you want the colour change to apply only to
the headings or to all cells including the headings? It's just that
you say if the heading contains a certain word and the cell below has
a certain value ...

Also, you could apply the conditional format once and then use the
Format Painter to apply this format elsewhere (to other columns, for
example).

Hope this helps.

Pete

 

[strawberry]

Hi all,

I want to select all my data (headers and values), then apply a conditional
format:

I have headers in row 1 and values in the underlying rows. When a header has
the words ''back office'' in it and the value in the cell beneath it is
greater then 0.0, then I want the cell to be filled orange.

Going on, if the header shows the word ''break'' and the value in the cell
beneath it is equal to 0.25, I want the cell to be filled yellow.

I want to do the above for a whole range of headings. I can't apply a
conditional format to each collum because it's simply too much work (BIG
file).

I'm not sure if this is possible but I appreciate all input.

Thanks!

In answer to the first part, the way I'd do it is to have cell
somewhere else (perhaps on another worksheet) that only evaluates to
true if all the conditions are met and then point the conditional
format to that cell like this:
=IF([target_cell]="TRUE",1,0)

 

[Hendrik]

I want the formatting to apply to the cells below the heading, which are in
numerical format. I want to apply a condtional format on the value of the
numerical cells below the heading (e.g. if cell value is greater then 0) but
also based on the heading (e.g. if heading for that column contains the word
"break" in it. These headings would contain the word break but in a string of
text (eg 1st Break Shift 1, 2nd Break Shift 2 etc..)

 

[Pete_UK]

Highlight all the data below your headings that you want this to apply
to - assume this range is B2:M200. Ensure that the activecell is B2,
then Format | Conditional Formatting.

In the panel that pops up, select Formula Is instead of Cell Contents
and enter this formula in the adjacent panel:

=AND(ISNUMBER(SEARCH("break",B$1)),B2=0.25)

Click on the Format button, then the Patterns tab and select bright
yellow for this background. Click OK and then Add, and a second panel
will appear. Again select Formula Is and enter this formula:

=AND(ISNUMBER(SEARCH("back office",B$1)),B2>0)

Click on the Format button and select Orange as the background colour.
Click OK twice and your formats have been applied to that range. If
you want the format to apply to more cells, then use the Format
Painter.

Hope this helps.

Pete

 

[David Biddulph]

You could presumably simplify that to =([target_cell]="TRUE"), or go another
step and get the formula in the target cell to produce a logical, rather
than a text string "TRUE", and then you could just have =[target_cell] ?

 

[Hendrik]

It works perfectly! Thanks for saving me a lot extra time!

I've tried the other suggested solutions as well, thanks for that. However,
this one works best for me.

 

[Pete_UK]

Thanks for feeding back, Hendrik - glad to help.

Pete