Re: [libreoffice-users] Cell references - apply formats too?

Charles asked
 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 
https://docs.google.com/file/d/0B6LXy9sguZVkVDdMMXh3Y1dhQWs/edit?usp=sharing



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 
https://docs.google.com/file/d/0B6LXy9sguZVkclNGV2pjcEVYY1E/edit?usp=sharing
, 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 
colour.
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
The 
formula is simply $E$2=$C6, 
where E2 is the salesperson with max sales and C6 = salesperson, and assign 
the Max style
The 
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. 

Tom said
> 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
Errol 
 

-- 
To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
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