Ask Your Question
0

Conditional formatting for HYPERLINK formula

asked 2016-02-08 09:30:57 +0200

gtomorrow gravatar image

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.

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
0

answered 2016-02-08 18:36:53 +0200

pierre-yves samyn gravatar image

Hi

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

Regards

edit flag offensive delete link more

Comments

WE HAVE A WINNER!

Thank you very much, pierre-yves samyn.

gtomorrow gravatar imagegtomorrow ( 2016-02-09 07:49:18 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2016-02-08 09:30:57 +0200

Seen: 1,167 times

Last updated: Feb 08 '16