SharePoint Calculated Column formula generator
If you are a regular follower of this site, you know that I have a high regard for Christophe’s No Deployment Solutions at Path to SharePoint. I use his solutions in the live online workshops, especially when it comes to color coding lists and calendars.
One of the problems with “rolling your own” when it comes to these solutions is that most people are novices when it comes to creating forumulas. Dessie Lunsford has provided a tremendous resource when it comes to Taming the Elusive Calculated Column, but when you need a quick one-off to setup a color coded list, it’s tough to spend an hour hunting and pecking for the right formula.
Help is here with Christophe’s online calculated column formula generator. Choose the colors you want to display for each value in a Status column, press the button and “Voila!”, you’ ve got a forumla.
This is going to be a great addition to your tool kit when creating HTML forumulas in your calculated columns. Take a look and let me know what you think. I’m already using it and I only found it 15 minutes ago!
this is going to be very helpful, thank you!
Once I pick the colors, how do I add the formula to the site? Will this work with WSS 3.0?
Thanks
Frank – Read the HTML in the Calculated Column articles on Christophe’s site to see how to implement this solution. We also do this type of solution in the live online workshops for the Content Editor Web Part. The full schedule for the next set of workshops comes out tomorrow. — Mark
Wow – this is most helpful! Thanks for sharing it… now if I could figure out a way to use a calculated column to split an existing column data based on a separator (and not just based on LEFT or RIGHT operators that require a fixed number of characters) heheh
Robin – Did you check out Dessie’s column on Taming the Elusive Calculated Column. I seem to remember the solution you are looking for in there somewhere.
I’ve actually done it myself, but would have to dig back to remember what the operator is. It’s easy to do if the seperator is a unique value in the string… harder if not.
– Mark
Thanks Mark – I’ll have a look at that. Basically, the only delimiter in the existing column that I wish to “split” is a space.
The current values are like this:
2000-01 (CF)
2000-122 PRO
20001-77 (INT)
etc… what I need to do is split the first code (item number) from the second string which is the item type acronym…
I’ll have a look through the column and eBook by Dessie.
Thanks
Robin
Robin – Create two calculated columns, one called “Left” and one called “Right” with the original value stored in the “Original” column.
Left column formula:
=LEFT(Original,FIND(" ",Original)-1)Right formula:
=RIGHT(Original,FIND(" ",Original)+1)I found this in an old forum conversation Dessie and I had:
http://www.sharepointblogs.com/forums/p/18307/39375.aspx
Wow – thanks Mark, I’ll give that code a try tomorrow and get back to you. That FIND operator sure is powerful!
Hello, this is great — however, I’ve got a different situation needing color. I’ve got 24 people needing their own color for a personnel locator calendar. I know what the end is – I don’t know how to get there. Any assistance will be greatly appreciated.
David,
Create a custom column that defines who the item is for. I usually call it “Original Author” or something like that.
Create a calculated column that uses the “Choose” function to dispense your colors.
Filter by the calculated column to determine the color displayed in the calendar or list.
Hope that gets you started, because I don’t have time for much more :-)
… and Jim Bob has a nice screencast showing how to use the Choose function to color code your calendar items.
http://www.endusersharepoint.com/?p=1563
– Mark
Hello Mr. Miller,
Thank you so much for your time and reply. I realize your time is valuable. If you get a few more moments you can afford me I will be especially grateful.
Item 1 – I’ve created the custom column – should it be a look up, a choice (with 24 names) or does it matter.
Item 2 – create a calculated column – can you please send me a sample of this so I can plug in where I need to. I have this as a start: =”<DIV style=’font-weight:bold; font-size:24px; color:”&CHOOSE
Am I in the right direction?
Item 3 – I don’t understand – can you break that down for me please? (elementary terms).
Again, I realize you’re very busy — your assistance is truly appreciated.
Thank You
> Item 1 – I’ve created the custom column – should it be a look up, a choice (with 24 names) or does it matter.
I would use a look up list for that number of items. Much easier to manage.
> Item 2 – create a calculated column – can you please send me a sample of this so I can plug in where I need to.
Look at Jim Bob’s screencast referred to above. He goes through the process step-by-step.
Regards,
Mark
item 1 – Unfortunately lookup columns cannot be used in formulas, so it’ll have to be a choice column.
item 2 – 24 “items” is a lot, so you won’t be able to use IF statements (at least not in one single column). The easiest way would be to assign a number to each person (1. Jane Doe, 2.etc.).
Christophe – If a calculated column can be used in a formula in another calculated column, can you point to the lookup column, use that as the value and then calculate off of the second column?
In the case you described, you would then be able to calculate off the number, formatting using the choose function.
– Mark
You can’t, as the first calculated column cannot point to the lookup in the first place.
A workaround would be to use a workflow to push the lookup to a standard text column, but it sounds like an overkill here.
@David: to precise my first reply, the usual recommendation for color coding is ~6 colors max. With 24 choices you are going to give your users a hard time.
Okay – our Division (24 total people) is made up of 6 branches. Can I assign a color for each branch then have the name of each person in each branch defined – Branch A = green = Smith, Jones, Johnson, Weaver; Branch B = Yellow = Doe, Miller, Daniels; and so on. So if Smith makes an entry to the calendar his/her name will appear in Green and if Miller makes an entry it will be yellow?