Recent

SharePoint Calculated Column Hyperlinks

SharePoint Calculated Columns or fields can be used to create clickable links, these links can also be configured to open in a new tab.  To make the URLs clickable, you have to save the Calculated Column as a Number data type.  I know this is totally crazy, but the number field type will for some reason render html in calculated fields.

Creating clickable Calculated Column links

In this example our list has a single line of text field called ‘Search Topic’, this is the topic you should search for if you want to find more related information about the list item.  What we want to do is display that Search Topic as a clickable link that does a google search for that topic when someone clicks on it.
Calc-Field-Links-16-1

1. Click on the List/Library tab, click on the List/Library Settings option.
Calc-Field-Links-16-2

2. On the Settings page, click on the Create Column option.
Calc-Field-Links-16-3

3. Enter a name for the Column, choose the Calculated type.
Calc-Field-Links-16-4

4A.  Enter the formula in the following format:
=”a href='{Insert URL Here}’>{Text to display}</a>”
Inserted Columns must have double quotes and ampersands: “&[Inserted Column]&”
Example Formula:
=”a href='{Insert URL Here}”&[Inserted Column]&”‘>{Text to display}</a>”
=”<a href=’https://www.google.com/#q=”&%5BSearch Topic]&”‘>Google Search</a>”
Calc-Field-Links-16-5

4B. Example where list column is used for text displayed in Calculated Column
Add “&[Inserted Column Name]&” between > AND </a>
Calc-Field-Links-16-6

4C. Example where link will open in a new tab
Add target=’_blank’ between <a AND href=
Calc-Field-Links-16-7

Completed Example: We now have 3 different calculated fields that all do a google search for the text entered into the items ‘Search Topic’ field:
Calc-Field-Links-16-9

If your inserted column is blank

If you are using a column in your URL and if this column is not required, this could create a URL that doesn’t work.  Because your URL will only be partially completed as the inserted column reference will be blank.

For example let’s say we deleted the Search Topic for Test #2 in our list, example 4B will now also be blank because it was set to show the Search Topic as the link text but example 4C will still have a link, but the link won’t work because it’s missing the search topic:
Calc-Field-Links-16-12

Fix for examples 4A and 4C

Since both of these examples use static text for their links, we will need to edit the formula to ensure that the link text is only displayed if the column (Search Topic) actually has a value entered.

On our calculated column formula, we will need to a add an IF ISBLANK operator.
Add IF(ISBLANK([Inserted Column Name]),””, between = AND “<a
Also add a ) at the very end of the formula:
Calc-Field-Links-16-10

Now the calculated column will be blank if the Inserted Column is blank:
Calc-Field-Links-16-11

More Calculated Column Tutorials

Visit the SharePoint Calculated Column Overview for a full list of calculated column tutorials.

About Nick Hurst (84 Articles)
SharePoint and Nintex developer who strives to find easy to deploy and maintain solutions for business problems.

2 Comments on SharePoint Calculated Column Hyperlinks

  1. Thanks so much for this tutorial. It really helped me.

    Like

  2. Rob Buurman // May 9, 2017 at 6:48 pm // Reply

    Thanks, this was exactly what i was looking for

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: