Is it possible to have a cell on one worksheet that references a
cell on another worksheet to also reflect the FORMAT of the
referenced cell? Ie, if the referenced cell has a red background,
the cell referencing it should also have a red background?
Yes, using conditional formats.
First create format styles with F11, then tell the cell to apply the
format appropriate to the value of a referenced cell.
see example at
Brian Barker said
this appears to show how the format of
the destination cell can be made to depend on the value, not the
format, of another cell ... it doesn't quite do what I think
the questioner is asking for.
Thanks. Yes, ideally, the destination would emulate the target based on raw
format alone. Then your scenario would come to life of a cell's colour
changing because of a direct change elsewhere. This would be possible if
cell() returned format or style info. Until the devs give this, the info
gap is keying a colour to a text or number, using styles and vlookup. Once
done, a change in the target's format (not just value) can change the
destination's format (as Charles asked for). This can be economically
achieved using conditional formatting.
See example at
, for 2 ways of auto-changing destination colours, 1 based on the target's
format and 1 on its value.
0. Create the data table. In the example, 10 salespersons sell 30 products
in 12 states on sheet "Data".
# - depending on format info of target cell
1a. Create colour coded styles. In the example, there are 7, named for the
rainbow, on sheet "Key".
1b. Identify the reference. In the example, colours are linked to text -
the names of the 10 salespersons on sheet "Key".
1c. Conditional-format the column of salespersons in the data table.
The formula is simply VLOOKUP(C6,Colours,2,1)="Red" etc for the set of
colours, where Colours = the table of salespersons and their allocated
This then colours the names in the list on another worksheet (Data), based
on the format choices for the target in the first worksheet (Key).
Note that though a format change in the target cell is automatically
emulated elsewhere, it is the value of the target cell rather than the
colour of the target cell that carries the info the referenced cell needs.
# 2 - depending on value of target cell
2a. Create colour coded styles. In the example, there are 2, Min and Max.
2b. Identify the reference. In the example, colours are linked to values -
the min and max sales totals.
2c. Conditional-format the table of sales data
formula is simply $E$2=$C6,
where E2 is the salesperson with max sales and C6 = salesperson, and assign
the Max style
formula is $E$3=$C6,
where E3 is the salesperson with min sales and C6 = salesperson, and assign
the Min style
This then colours the numbers in the list on another worksheet (Data),
based on the choices for the target in the first worksheet (Key).
In the example, both methods are shown. Use F9 to cycle through scenarios.
The salespersons adopt the colours assigned to them via #1 (format/value)
and their sales adopt the colours assigned to them via #2 (value). The
sales-manager can simultaneously see who is who and who did how.
It might be worth asking on the devs list to see if they have some quick
way of diving into the code or it might be worth posting a bug-report
about it as a "feature request". There might already be a bug-report
although i seem to remember the last person asking about this got his task
done some other way.
Agree. If cell() can show format|style info, then it will be possible to
directly satisfy Charles without this complexity.
hope this helps
To unsubscribe e-mail to: email@example.com
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted
- Re: [libreoffice-users] Cell references - apply formats too? · errol -AT- xe4.org
Impressum (Legal Info)
: Unless otherwise specified, all text and images
on this website are licensed under the
Creative Commons Attribution-Share Alike 3.0 License
This does not include the source code of LibreOffice, which is
licensed under the Mozilla Public License (MPLv2
"LibreOffice" and "The Document Foundation" are
registered trademarks of their corresponding registered owners or are
in actual use as trademarks in one or more countries. Their respective
logos and icons are also subject to international copyright laws. Use
thereof is explained in our trademark policy