Conditional Formatting Problem

[Peter]

Is there a way to make conditonal formatting work when there is a
formula in the cell?

Conditional foramtting works if there is no formula in the cell. If
one sheet is linked to another that doesn't work either.

Is there a way to get around this?

Under conditonal formatting there is a conditon "formula is." What
would you put in there to have the cell format they you would like?

 

[Alexander Wolff]

There are two different things:

a) the cell, containing a cell formula, a value or nothing

b) the condition, containing a boolean formula (formula is)

- b) can refer to a), but a) referring to a) makes it circular.
- for b) it does not matter, whether a) is a formula or a value

Example:

A1: 50 CondFrmt =A1>40 here works the same as in
A1: =DATE(NOW())

 

[MartinW]

Hi Peter,

Maybe this.
Sometimes you need to set the Formula is: part of
conditional formatting to the same formula as you
have in your cell, rather than working off the value
that it returns.

HTH
Martin

 

[RAGdyer]

CF (Conditional Formatting) can work on a cell that contains a formula of
it's own, completely separate from the CF, *and* it can work spanning other
sheets.

Post back with some specific questions if you need assistance creating CF
formulas.

 

[Peter]

Here is a formula I have in a cell. I drag it down over many cells,
so it changes slightly as you drag it down. If I try to paste it into
the conditional formatting box it won't let me do that.

=IF(K17="","",IF(F17=J17,0.5*(H17*K17),IF(G17=J17,0.5*(I17*K17)))-(H17+I17))

So, I'm not really sure how to proceed.

 

[Cuperman007]

Hi Peter,

You can use the FormatPainter button to copy Conditional Formatting.

Select the cell(s) you want to copy from. Click the FormatPainter
button. Selec the cells you want to apply the formats to.

Best
Mark

 

[MartinW]

Hi Peter,

Your formula returns one of the following,

""
0.5*(H17*K17) - no need for the brackets here
0.5*(I17*K17)-(H17+I17)

so the formulae to use in CF
are either
=""
=0.5*H17*K17
=0.5*(I17*K17)-(H17+I17)

or maybe all three as Condition 1, Condition 2, Condition 3
As mentioned by Mark you can use the Format Painter to drag them down
or you can select all of your cells first before you apply the CF and
they will automatically adjust down the sheet.

HTH
Martin

 

[MartinW]

Woops! You didn't actually say which cell is the starting cell for
your formula so say were placing it in M17 then your formulae
in CF should be

=M17=""
=M17=0.5*H17*K17
=M17=0.5*(I17*K17)-(H17+I17)


HTH
Martin

 

[MartinW]

And just as an aside, there is another set of un-needed brackets

=M17=0.5*(I17*K17)-(H17+I17)
is the same as
=M17=0.5*I17*K17-(H17+I17)

Of course, none of these extra brackets do any harm and can be left in
place.
Actually I sometimes find it better to use extra un-needed brackets as it
can
make your formula easier for others to follow.

HTH
Martin

 

[RAGdyer]

What does the formula you posted have to do with CF ? ? ?

Your formula will return values!
CF formulas should return logicals (True, False).

When the return is "True", the chosen format is invoked.

Either you don't understand CF, OR ... I don't understand you!

Perhaps you're using the term "CF" by mistake, and you actually mean
something completely different.

 

[Peter]

You know what? All of you guys helped me. I knew it would be hard to
explain what I was doing, but by piecing together all of the
responses, I got my spreadsheet to do exactly what I want. It took
some experimenting, but CF was the answer.

Thanks to all,