I hope you have found some useful applications of my previous posts and created a couple lists relying on HTML calculated columns.
At this point, let’s not forget that all the customizations were entirely done through the SharePoint UI (as opposed to customizations done with SharePoint Designer for example). Which means that you now have the possibility to save your lists as templates and reuse them.
The steps are easy, let me review them quickly:
1/ Customize the list:
– create your SharePoint list
– create the calculated columns with the HTML formulas
– add the CEWP and the script on the display page (dispform.aspx), under the list
– modify the default view, add other views as needed
2/ Save the list as template
– Settings | List Settings | Save list as template. The template has a .stp format
– click on “go to the list template gallery”
– Click on the template name
– In the pop-up window click on save, and save it to your local drive
3/ Use the template:
– In the new site collection: go to Site Actions | Site Settings | Galleries | list templates
– Upload the template
– go to Site Actions | Create, the template is available in the list!
The steps in green are only needed if you want to use the template in another site collection, or send it to a friend.
Note that the HTML rendering will only work in the default list views (dispform.aspx, etc.). If for example you drop the list on your home page, you’ll also need to add a CEWP with the script.
First off, this is great work that you have done. I have really used your work and have made a great Dashboard based upon this.
I added to your Progress Bar example. I am looking at several fields. I am looking at the [% Completed], [Process Status], and [Progress].
[Edited by Christophe]
Peter’s example is now available as a separate post:
[/Edited by Christophe]
Keep up the great work and hope this is helpful to others.
I created a calculated field Score that mulitplies another field and then gives total (this works fine). I have created another calculated field Total Score to add html background color from the – the calculated field Score….
I have the html as indicated in Applying Color to sharepoint lists – I want to use nested If statements (IF(score>21, “Green”,IF(score45,”Red”))) then use the rest of the code for background in your list. I have not been able to get this to work…thoughts on this. Thank you
Lynnda, e-mail me your formulas and I’ll take a look (Christophe@PathToSharePoint.com).
I have a question and maby someone knows what code to use in a “calculated colomn”:
If you want to detemine the selection in a colomn of the choice type you can number the items and then use choose to determine what is selected:
 Item #1
 Item #2
 Item #3
 Item #4
for example: =CHOOSE(RIGHT(LEFT([Colomn],2),1),”#1″,”#2″,”#3″,”#4″)
But in this example you need to “number” the selection to accomplish this. How can I (without using al lot of nested if’s) determine what option is selected?
An exercise: I have a choice list with 4 colors: green, blue, yellow, red. How can I determine the selection without ifs (nested or not)?
The answer to last week’s exercise: use the CHOOSE function together with the LEN function, which returns the number of characters in a text string. So 3 is for red, 4 for blue, etc.
The same method can be applied to:
– High/Medium/Low (4/6/3)
– Yes/No (3/2)
– Open/Resolved/Closed (4/8/6)
Alex, see if this works for you. However, if you expect your choice list to be updated in the future, I think that numbering the items remains the safest choice.
That’s a need trick.
I solved my problem by using the first 2 letters of each choice so the function would still be “readable”:
=Created+IF(LEFT([Request type];2)=”No”;0;IF(LEFT([Request type];2)=”Qu”;2;IF(LEFT([Request type];2)=”Pr”;1;IF(LEFT([Request type];2)=”Mi”;5;99))))
The list is:
Problem or Issue
Where the response time for Notification is 0 days, Question is 2 days, etc.
Thanks for you response!
OK. In your case, my suggestion would work too (12/8/16/13) if you choice list is stable.
The maximum for a CHOOSE value is 29.
I am trying to use traffic lights based on the results of 2 columns.
Would you please assist me.
First column is system importance (gold, silver bronze). Second column is DR Present (yes,no)
I started with this (I am not a programmer so sorry if it looks bad). Tried using IF(AND but couldn’t get it to work.
I only get green showing, the red does not come up. I haven’t done the silver and bronze categories yet.
=”<DIV style='font-weight:bold; font-size:24px; color:"&IF([System Criticality]="(1) gold",IF([DR@Bondi]<="yes","green",IF([System Criticality]<="(1) gold",IF([DR@Bondi]•”