How to create hyperlinks
Sharperlight provides users the ability to create and incorporate hyperlinks in Excel. By using an expression in the query builder, users are able to create a hyperlink that will load when clicked upon.
- In order to force Excel to treat our output as a Formula (and specifically a hyperlink), we need to ensure the expression datatype is set to HTML, and we need to make use of the =HYPERLINK() function.
- Since our expression is first resolved by the Sharperlight Expression engine, and then Excel after, we need to ensure our special characters (mainly quotes in this case) are not automatically intercepted by Sharperlight. To safely use a quote, use the ChQuote() function.
- Rather than using the ConcatString() function, we can simply use the + operator to concatenate any strings that are wrapped in quotes. Both methods will work, it just comes down to personal preference.
- When using an mdTable formula, Excel will try to help us by auto-cascading the formula, which leads to the same link on every row, because Excel has no idea that we are planning on introducing our own equivalent to ‘relative cell referencing’, which is supposed to place a unique value in each row.
Therefore, rather than using the row level values for the analysis code in Sharperlight (e.g. {%Output}), we will need to use a native excel table references (e.g. [@[T9 – V1 Ref]]). This forces Excel to update the values for each row.
