Re: [libreoffice-users] Cell references - apply formats too?
On 2013-07-18 10:52 PM, Brian Barker b.m.bar...@btinternet.com wrote: What you can do, of course, is to set up another column or row in which you put suitable values which can then control conditional formatting of both the target cell and the referencing one. To change the background of both, you merely need to change the controlling values. These could even be colour names: red, green, and so on. Doesn't do what we need (see my last response to Errol), but thanks for trying... -- 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
Re: [libreoffice-users] Cell references - apply formats too?
Hi :) I think what you are asking for can't be done. 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. Regards from Tom :) From: Tanstaafl tansta...@libertytrek.org To: users@global.libreoffice.org Sent: Friday, 19 July 2013, 11:44 Subject: Re: [libreoffice-users] Cell references - apply formats too? On 2013-07-18 10:52 PM, Brian Barker b.m.bar...@btinternet.com wrote: What you can do, of course, is to set up another column or row in which you put suitable values which can then control conditional formatting of both the target cell and the referencing one. To change the background of both, you merely need to change the controlling values. These could even be colour names: red, green, and so on. Doesn't do what we need (see my last response to Errol), but thanks for trying... -- 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
Re: [libreoffice-users] Cell references - apply formats too?
At 06:44 19/07/2013 -0400, Charles Marcus wrote: On 2013-07-18 10:52 PM, Brian Barker wrote: What you can do, of course, is to set up another column or row in which you put suitable values which can then control conditional formatting of both the target cell and the referencing one. To change the background of both, you merely need to change the controlling values. These could even be colour names: red, green, and so on. Doesn't do what we need (see my last response to Errol), but thanks for trying... We may be at cross-purposes here: your last reply was about changing protected cells. This reply was about your other query: copying the format of a referenced cell. But no matter . Brian Barker -- 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
Re: [libreoffice-users] Cell references - apply formats too?
On 2013-07-19 9:11 AM, Brian Barker b.m.bar...@btinternet.com wrote: At 06:44 19/07/2013 -0400, Charles Marcus wrote: On 2013-07-18 10:52 PM, Brian Barker wrote: What you can do, of course, is to set up another column or row in which you put suitable values which can then control conditional formatting of both the target cell and the referencing one. To change the background of both, you merely need to change the controlling values. These could even be colour names: red, green, and so on. Doesn't do what we need (see my last response to Errol), but thanks for trying... We may be at cross-purposes here: your last reply was about changing protected cells. This reply was about your other query: copying the format of a referenced cell. But no matter . Yeah, sorry, once I realized that there was no way currently to do this like Excel, I asked about copying the format of the referenced cell as an alternate way of accomplishing the goal. Too bad neither is possible... I'll go open Bug/Feature requests for all 3 of these issues as soon as I have time... Thanks again -- 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
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+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
[libreoffice-users] Cell references - apply formats too?
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? Thanks -- 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
Re: [libreoffice-users] Cell references - apply formats too?
Hi :) I think we had a thread about this maybe 6months-1year ago? I think the up-shot was that it's not possible and maybe better to have a 2nd column to indicate colour. Regards from Tom :) From: Tanstaafl tansta...@libertytrek.org To: users@global.libreoffice.org Sent: Thursday, 18 July 2013, 15:53 Subject: [libreoffice-users] Cell references - apply formats too? 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? Thanks -- 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 -- 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
Re: [libreoffice-users] Cell references - apply formats too?
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. In the example at https://docs.google.com/file/d/0B6LXy9sguZVkVDdMMXh3Y1dhQWs/edit?usp=sharing c2 - n2 are months Jan to Dec, b3 - b12 are 10 salespersons and c3 - n12 are their monthly sales. We want to use colours to quickly see who sold the most and least each month. c15 - n15 show the min sales in each column (and are formatted white on red. This is per style named Min) c17 - n17 show the max sales in each column (and are formatted white on blue. This is per style named Max) In the conditional formatting for each cell c3 - n12, 1. apply style Min if the cell contents = cell in row 15 and 2. apply style Max if the cell = row 17. What you achieve is 2 cells in each column automatically turning red or blue if they match other cells based on their value, allowing high or low achievers to be immediately recognised. 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
Re: [libreoffice-users] Cell references - apply formats too?
At 10:53 18/07/2013 -0400, Charles Marcus wrote: 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? If you mean so that you can arbitrarily change the background of the target cell and the background of the referencing cell will follow suit, I think not: there appears to be no way to test the formatting of a cell or to retrieve the formatting details. At least, you can retrieve numeric formatting using the CELL() function, but not other aspects of formatting, including background colour. Unless someone knows better. What you can do, of course, is to set up another column or row in which you put suitable values which can then control conditional formatting of both the target cell and the referencing one. To change the background of both, you merely need to change the controlling values. These could even be colour names: red, green, and so on. I trust this helps. Brian Barker -- 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
Re: [libreoffice-users] Cell references - apply formats too?
At 00:52 19/07/2013 +0200, Errol Goetsch wrote: [Someone 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. In the example at https://docs.google.com/file/d/0B6LXy9sguZVkVDdMMXh3Y1dhQWs/edit?usp=sharing c2 - n2 are months Jan to Dec, b3 - b12 are 10 salespersons and c3 - n12 are their monthly sales. We want to use colours to quickly see who sold the most and least each month. c15 - n15 show the min sales in each column (and are formatted white on red. This is per style named Min) c17 - n17 show the max sales in each column (and are formatted white on blue. This is per style named Max) In the conditional formatting for each cell c3 - n12, 1. apply style Min if the cell contents = cell in row 15 and 2. apply style Max if the cell = row 17. What you achieve is 2 cells in each column automatically turning red or blue if they match other cells based on their value, allowing high or low achievers to be immediately recognised. Sorry to be a wet blanket, but 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. If I change the format of your Min and Max value rows - say to give them green backgrounds - the cells highlighted by conditional formatting still appear in red and blue - according to your Min and Max cell styles. It's very pretty and well designed and no doubt useful, but it doesn't quite do what I think the questioner is asking for. Brian Barker -- 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
