I was looking to have this view available in "all items" view (Default style view) on a list called VIP Updates. I have a calculated column labeled Stoplight in which the color coded circles are supposed to appear. The data in which the Stoplight column derives its information from is labeled "Cases." The cases column has either a 1,2,3,4 or 5 in it. I was looking for it to come out like this:

1 - Green 2 - Red 3 - Yellow 4 - Orange 5 - Blue

An example of the stoplight:

enter image description here

Current formula

="<DIV style='font-weight:bold; font-size:24px; color:"&CHOOSE(RIGHT(LEFT(CASES,2),1),"red","orange","green")&";'>•</DIV>"

I found a couple of useful answers for KPIs:

Using calculated fields as KPI

Using Today and Me in Calculated Columns

Edits based on comments: Make sure you calculated column is formatted to return a number, as that's the trick for rendering HTML.

You can try making your calculated field just =(RIGHT(LEFT(CASES,2),1) and see what it returns. When you have this clause returning the numbers you expect, then you can add the rest of the html around it. That will help focus in on whether it's a problem with the Right/Left clause or the HTML.

|improve this answer|||||
  • ="<DIV style='font-weight:bold; font-size:24px; color:"&CHOOSE(RIGHT(LEFT(CASES,2),1),"red","orange","green")&";'>•</DIV>" – vier111 Jul 24 '17 at 20:34
  • I have the calculated column formatted to return the value as a number. – vier111 Jul 26 '17 at 14:08
  • I don't have site collection administrator privileges. – vier111 Jul 26 '17 at 14:58
  • Are the values of your "Cases" field actually "1 - Green", "2 - Red", "3 - Yellow", "4 - Orange", and "5 - Blue", or are they something else? If they are actually this, your Left/Right clauses would return a space. – Kalea Jul 27 '17 at 19:00
  • its Version 2013 – vier111 Jul 28 '17 at 20:30

Note: Please mention the version of SharePoint you are using, so that its easy to provide the suggestions/answers

Is this for SharePoint Online? If yes, HTML within a calculated field will not work anymore. Effective Jun 13 2017, this feature is disabled. Administrators can request an extension through September 10, 2017 at the latest.

For SharePoint 2013 & SharePoint 2016, this will be provided as a configurable option via the June 2017 and subsequent Public Updates.

Discussion on this topic can be found here - June 13th 2017 Microsoft blocked handling HTML markup in SharePoint calculated fields - how to get the same functionality back

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.