Ask LibreOffice - RSS feedhttps://ask.libreoffice.org/en/questions/Questions and answers for LibreOfficeenTue, 09 Feb 2016 07:49:18 +0100Conditional formatting for HYPERLINK formulahttps://ask.libreoffice.org/en/question/64111/conditional-formatting-for-hyperlink-formula/I have a Calc spreadsheet with 10 columns formatted as currency with an 11th column averaging the amount of the preceding 10 (=AVERAGE(Ax:Jx)). To some of the cells in these 10 columns I have applied a HYPERLINK formula linking to webpages, reason being that "Insert > Hyperlink" loses the currency formatting and becomes plain text, rendering the cell useless for the AVERAGE. I have defined a style ("Price hyperlink") for these HYPERLINK cells to differentiate them from non-HYPERLINK cells. So far I have been applying the "Price hyperlink" style cell by cell. Is there a way to apply a conditional format (in this case "Price hyperlink") to any cell with the HYPERLINK formula?
Thanks.Mon, 08 Feb 2016 09:30:57 +0100https://ask.libreoffice.org/en/question/64111/conditional-formatting-for-hyperlink-formula/Answer by pierre-yves samyn for <p>I have a Calc spreadsheet with 10 columns formatted as currency with an 11th column averaging the amount of the preceding 10 (=AVERAGE(Ax:Jx)). To some of the cells in these 10 columns I have applied a HYPERLINK formula linking to webpages, reason being that "Insert > Hyperlink" loses the currency formatting and becomes plain text, rendering the cell useless for the AVERAGE. I have defined a style ("Price hyperlink") for these HYPERLINK cells to differentiate them from non-HYPERLINK cells. So far I have been applying the "Price hyperlink" style cell by cell. Is there a way to apply a conditional format (in this case "Price hyperlink") to any cell with the HYPERLINK formula?</p>
<p>Thanks.</p>
https://ask.libreoffice.org/en/question/64111/conditional-formatting-for-hyperlink-formula/?answer=64132#post-id-64132Hi
You can use a formula like this: `ISNUMBER(FIND("HYPERLINK";FORMULA(A2)))`
From RTL in the formula▸we look for the word `HYPERLINK`▸ if it is found the result is a number;
See [HyperlinkCF.ods](/upfiles/14549528752769117.ods)
RegardsMon, 08 Feb 2016 18:36:53 +0100https://ask.libreoffice.org/en/question/64111/conditional-formatting-for-hyperlink-formula/?answer=64132#post-id-64132Comment by gtomorrow for <p>Hi</p>
<p>You can use a formula like this: <code>ISNUMBER(FIND("HYPERLINK";FORMULA(A2)))</code> </p>
<p>From RTL in the formula▸we look for the word <code>HYPERLINK</code>▸ if it is found the result is a number;</p>
<p>See <a href="/upfiles/14549528752769117.ods">HyperlinkCF.ods</a></p>
<p>Regards</p>
https://ask.libreoffice.org/en/question/64111/conditional-formatting-for-hyperlink-formula/?comment=64153#post-id-64153**WE HAVE A WINNER!**
Thank you very much, pierre-yves samyn.Tue, 09 Feb 2016 07:49:18 +0100https://ask.libreoffice.org/en/question/64111/conditional-formatting-for-hyperlink-formula/?comment=64153#post-id-64153