Conditional Formatting with relative cells

[HelenJ]

I have just taken over a spreadsheet that has a lot of pretty complicated
formatting on it, the trouble is I know some of it isn't working.

Here is an example:
=IF(M5<>"",IF(M5<(J5+0.0104166666666667),TRUE,FALSE),FALSE)

this is applied to a whole column, the trouble is it should really be
checking the value of M# and J# based on the row that the cell is in ie on eg
row 99 it should be:
=IF(M99<>"",IF(M99<(J99+0.0104166666666667),TRUE,FALSE),FALSE).

Is there any way of making conditional formatting formulae relative? (I'm
using 2007, but the spreadsheet will be used on machines using earlier
versions)

Thanks

 

[Bob Umlas]

If you select all the cells in the CF first, then use a relative reference
(no "$" for the row), it should work just fine. Use the active cell's row as
the sample to apply to the whole range.

 

[HelenJ]

Thanks for your swift answer Bob, but have I misunderstood you? CF means
conditional formatting? Because what I did was to select all the cells to
which I wanted to apply the conditional formatting, but then I am stuck
because the formula (that I posted) appears to have relative values in it,
but applying it to the whole range in one go they don't behave as relative,
they behave as fixed.

So you solution didn't change what I had already (unless I have
misunderstood you).

H

 

[Marcelo]

Helen what is the column or cell are you looking to format?

I have tested it and works.


--
pleae click yes if it was helpfull
regards from Brazil
Marcelo



"HelenJ" escreveu:

 

[T. Valko]

What is the range of cells you want to apply the conditional formatting to?

Here is an example:
=IF(M5<>"",IF(M5<(J5+0.0104166666666667),TRUE,FALSE),FALSE)

That formula can be reduced to:

=AND(M5<>"",M5<J5+TIME(0,15,0))

 

[HelenJ]

Thanks for the reduced formula, and the use of the TIME function, much
clearer .

The conditional formatting for this case, (there a lot of different versions
and I am picking my way through the whole sheet!) is applied to
=$M$5:$M$1940,$M$1942:$M$2289. I suspect this is a mistake and it should be
applied to =$M$5:$M$2289.

So in essence I am looking for conditional formatting that can examine the
cell that it is based on and one, or more, other relative cells to establish
the relevant condition.

Does that make sense?

By experimenting I have found that if I apply the condition to just one cell
and then paste the format to one more cell then it does make the formula
relative, but if I do it to a large range then it effectively becomes
absolute even though the formula isn't. Clearly I would like to avoid copy /
paste format over thousands of individual cells!

Thanks.

 

[T. Valko]

Try this:

Select the *entire range of interest* M5:M2289.

You can do this quickly by typing the range into the name box. The name box
is that little "box" immediately above the column A header. Click in the
name box, type the range M5:M2289 then hit Enter.

The range M5:M2289 will be select and cell M5 will be the active cell. The
active cell is the single cell in the selected range that is not shaded. The
formula to be used will be relative to the active cell.

With the range selected...

Goto Home tab>Styles>Conditional Formatting>Manage rules>New rule>Use a
formula to determine which cells to format
Enter this formula in the box below:
=AND(M5<>"",M5<J5+TIME(0,15,0))
Click the Format button
Select the desired style(s)
OK out

 

[HelenJ]

Many thanks for all your help. I've just realised, pretty much what you have
said, that the formula is relative to the first cell in the range - and of
course it doesn't appear to change.

My problem was that the formatting on this spreadsheet is so complicated and
several of the formulae are even more convoluted (7 or eight multiple levels
of interlinked nested ifs!) that I leapt to the conclusion it was the
conditional formatting that wasn't working.

Well I have now learnt a lot (and I feel much happier to sort out the rest
of this spreadsheet).

So thanks everyone for your help.

H

 

[T. Valko]

Good luck! Thanks for the feedback!