Conditional formatting cell copying?

Hylton_Conacher_ZR1H · July 12, 2011, 7:41pm

Hi all,

Is there a way to copy a particular cells conditional formatting rules
across a cell selection either in a row or a column?

I have a spreadsheet with three columns of numbers, two of which require
three conditional formatting rules on each and every cell row.

I know how to increase of the cell reference in a cell formula is
possible, but how can the cell values in the cell conditional formulae
also be updated incrementally?

If this is not possible, please give me some links as to where I can
suggest it for LibreOffice. This feature is also lacking on MS Excel
2003 and getting this implemented I am sure would be a MAJOR boost for
LibreOffice.

Regards
Hylton

David_Griffiths · July 12, 2011, 7:57pm

Hylton Conacher (ZR1HPC) wrote:

Hi all,

Is there a way to copy a particular cells conditional formatting rules
across a cell selection either in a row or a column?

I have a spreadsheet with three columns of numbers, two of which require
three conditional formatting rules on each and every cell row.

I know how to increase of the cell reference in a cell formula is
possible, but how can the cell values in the cell conditional formulae
also be updated incrementally?

If this is not possible, please give me some links as to where I can
suggest it for LibreOffice. This feature is also lacking on MS Excel
2003 and getting this implemented I am sure would be a MAJOR boost for
LibreOffice.

Regards
Hylton

Hello Hylton

Set up your conditions as required in one cell and confirm they all work as
expected. Then you can copy the cell, select paste special and you will get a
default of paste all. This will include the conditional formats in my test.

Tested using:
LibreOffice 3.3.3
OOO330m19 (Build:301)
tag libreoffice-3.3.3.1
openSUSE 11.4 64 bit

HTH
Regards
Dave

Regina · July 12, 2011, 7:59pm

Hi Hylton,

Hylton Conacher (ZR1HPC) schrieb:

Hi all,

Is there a way to copy a particular cells conditional formatting rules
across a cell selection either in a row or a column?

I have a spreadsheet with three columns of numbers, two of which require
three conditional formatting rules on each and every cell row.

I know how to increase of the cell reference in a cell formula is
possible, but how can the cell values in the cell conditional formulae
also be updated incrementally?

I do not understand your problem. Please give an example of the condition, which you want to copy and the cell address, to which it is applied actually.

Do I understand you correct, that copying itself is no problem for you, but how to write the condition?

Kind regards
Regina

Hylton_Conacher_ZR1H · July 13, 2011, 6:59pm

Hi Dave,

Thanks for the answer, and sorry for the belated reply, however it
doesn't work using LibreOffice 3.3.1
OOO330m19 (Build:8)
tag libreoffice-3.3.1.2 On OpenSuse 11.0

You are quite correct that the conditional formulae are copied too,
however the formulae still refer to the same cell references given in
the copied cell.

In other words if you copied cell C3 and pasted special on C4:c10, the
conditional formatting would be copied but the cell references in cell
C10 for the conditional formatting would point to the same cell as those
referenced in cell C3.

Please confirm if this is the case.

Regards
Hylton

Gerard_Fargeot · July 13, 2011, 7:33pm

Hylton Conacher (ZR1HPC) wrote:

In other words if you copied cell C3 and pasted special on C4:c10, the
conditional formatting would be copied but the cell references in cell
C10 for the conditional formatting would point to the same cell as those
referenced in cell C3.

Formulae in Format > Conditional formatting used the same rules as Formulae
in a cell.

Used Absolute, Relative, or Mixed adressing. ($A$1,A1,$A1,A$1)

Gérard

Johnny_Rosenberg · July 13, 2011, 7:34pm

Hi Dave,

Thanks for the answer, and sorry for the belated reply, however it
doesn't work using LibreOffice 3.3.1
OOO330m19 (Build:8)
tag libreoffice-3.3.1.2 On OpenSuse 11.0

You are quite correct that the conditional formulae are copied too,
however the formulae still refer to the same cell references given in
the copied cell.

In other words if you copied cell C3 and pasted special on C4:c10, the
conditional formatting would be copied but the cell references in cell
C10 for the conditional formatting would point to the same cell as those
referenced in cell C3.

Remember that the $ works the same way in conditional formatting like
it does in cell formulas, for example $A$1. This reference won't
change when copying the cell, A1 would.

Kind regards

Johnny Rosenberg
ジョニー・ローゼンバーグ

Regina · July 13, 2011, 8:00pm

Hi Hylton,
Hylton Conacher (ZR1HPC) schrieb:

Hi all,

Is there a way to copy a particular cells conditional formatting rules
across a cell selection either in a row or a column?

Yes. Copy a cell, which has the conditional formatting assigned. Mark the target and goto Edit> Paste Special.
Unselect all but 'Format' in the section 'Selection' and mark 'Add' in the section 'Operations'.

I have a spreadsheet with three columns of numbers, two of which require
three conditional formatting rules on each and every cell row.

You can use a formula with ISODD(ROW()) and ISEVEN(ROW()) in the condition and so use the same condition for each row.

I know how to increase of the cell reference in a cell formula is
possible, but how can the cell values in the cell conditional formulae
also be updated incrementally?

?? Please post an example of such a condition. You might have to use the type "Formula is" instead of "Cell value is" in the condition.

Kind regards
Regina

Hylton_Conacher_ZR1H · July 13, 2011, 8:19pm

Hi Regina,

See my earlier reply to Dave in addition to the below:.

Copying and writing the conditions are not the problem.

Getting the condition to automatically increment is the problem e.g. I
have cell A1 that has the formula to add the contents of cells B1, C1
and D1. Cell A1 also has 3 conditional formulas that relate its value to
cells B1, C1 and D1 and apply certain colour coding to the A1 cell.

The formula is cell A1 is the same until cell a10, with just the
respective cell references changing i.e. on cell A5 the formula would
add cells B5, C5 and D5. What I require is that the conditional
formatting applied to cell A1 also be applied to cell A5 but with the
conditional formulae having been incremented i.e. so that the correct
formatting is shown, as per the conditional formulae for the values on
that row i.e. b5, C5 and D5, when the value in A5 is compared to cells
B5, C5 and D5.

I trust that answers the query.

Regards
Hylton

Regina · July 13, 2011, 8:50pm

Hi Hylton,

Hylton Conacher (ZR1HPC) schrieb:

Hi Regina,

See my earlier reply to Dave in addition to the below:.

Copying and writing the conditions are not the problem.

Getting the condition to automatically increment is the problem e.g. I
have cell A1 that has the formula to add the contents of cells B1, C1
and D1. Cell A1 also has 3 conditional formulas that relate its value to
cells B1, C1 and D1 and apply certain colour coding to the A1 cell.

Let see, whether I understand you now.
content of A1 is =B1+C1+D1

Desired condition in A1 is for example: make red if B1 negativ, if not make yellow if C1 negative, if not make green if D1>10.

The formula is cell A1 is the same until cell a10, with just the
respective cell references changing i.e. on cell A5 the formula would
add cells B5, C5 and D5. What I require is that the conditional
formatting applied to cell A1 also be applied to cell A5 but with the
conditional formulae having been incremented i.e. so that the correct
formatting is shown, as per the conditional formulae for the values on
that row i.e. b5, C5 and D5, when the value in A5 is compared to cells
B5, C5 and D5.

If you use relative addresses in your condition, the condition is adapted when copied same as the cell formulas.

Example:
Condition for A1 is
1.condition: "Formula is" =B1<0
2.Condition: "Formula is" =C1<0
3.Condition: "Formula is" =D1>10

When you now copy this condition to A8, you will get for A8 the condition
1.condition: "Formula is" =B8<0
2.Condition: "Formula is" =C8<0
3.Condition: "Formula is" =D8>10

It that, what you are looking for?

Kind regards
Regina

David_Griffiths · July 13, 2011, 9:04pm

Hylton Conacher (ZR1HPC) wrote:

Hi Dave,

Thanks for the answer, and sorry for the belated reply, however it
doesn't work using LibreOffice 3.3.1
OOO330m19 (Build:8)
tag libreoffice-3.3.1.2 On OpenSuse 11.0

You are quite correct that the conditional formulae are copied too,
however the formulae still refer to the same cell references given in
the copied cell.

In other words if you copied cell C3 and pasted special on C4:c10, the
conditional formatting would be copied but the cell references in cell
C10 for the conditional formatting would point to the same cell as those
referenced in cell C3.

Please confirm if this is the case.

Regards
Hylton

Hi Hylton

No problem with the belated reply. As mentioned in other replies, the cell
references can be relative, absolute or mixed so I think what you want is
achievable.

Dave

jorge1 · July 13, 2011, 11:30pm

Hi

You can do it like this:

1) Select two columns and all rows that you need format.
2) Select Menu-Format-Conditional Format
3) Make the format that you need
4) Accept

I know that this conditional format is for all area selected and it
didn't show a special cell formula o condition... It looks transparent.

Regards,

Jorge Rodríguez

Hylton_Conacher_ZR1H · July 16, 2011, 6:28am

Tnx all,

The solution was to reference the cells without the $ sign before them.
If I copied that cell and selected the cells I wanted to apply the
conditional format to, selected Paste Special and selected Formats and
Formula, it worked a treat.

Thanks to whoever thought of the relative/absolute references as well as
the Paste Special.

Regards
Hylton

Tom_Davies · July 16, 2011, 9:05am

Hi
Congrats for fixing it Nicely done
Regards from
Tom