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+unsubscr...@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