CODEWITHSUNDEEP
functionformattingconditional-statementslibreoffice-calc
Campa
Campa

Reputation: 4505

Parametrized conditional formatting

I am trying to dynamically set the Style of a row in Calc.

After giving up using a macro (see Changing cell background color in LibreOffice), I tried with Conditional Formatting rules (Format --> Conditional Formatting).

I would like to set a parametrized Range, so that the conditional formula is applied separately for each row.

I tried using the ADDRESS function to express ranges:

A1:C6    --->   ADDRESS(1,1):ADDRESS(6,3)

But that doesn't work. This reduces down to testing single cell functions, for instance:

    .------------------- -- -
    |  A  |  B  |  C  |  D
.----------------------- -- -
| 1 | aaa | bbb | ccc | 
.--------------------- -- -

[ok] =ADDRESS(1,3)
  \____$C$1

[ok] =CELL("contents", C1)
  \____ccc

[error] =CELL("contents", ADDRESS(1,3))
   \____#REF!

As you see, the last function gives a #REF! error (instead of ccc).

Any suggestion?

Upvotes: 4

Views: 209

Answers (1)

tohuwawohu
tohuwawohu

Reputation: 13618

ADDRESS returns the reference as text. CELL expects a reference. So you need to translate the reference string using INDIRECT:

=CELL("CONTENTS", INDIRECT(ADDRESS(1,3)))

Upvotes: 3

Related Questions