Supporters of End User
Web

SharePoint Calculated Column formula generator

 
Item is currently unrated. Press SHIFT+ENTER to rate this item.1 star selected. Press SHIFT+ENTER to submit. Press TAB to increase rating. Press SHIFT+ESCAPE to leave rating submit mode.2 stars selected. Press SHIFT+ENTER to submit. Press TAB to increase rating. Press SHIFT+TAB to decrease rating. Press SHIFT+ESCAPE to leave rating submit mode.3 stars selected. Press SHIFT+ENTER to submit. Press TAB to increase rating. Press SHIFT+TAB to decrease rating. Press SHIFT+ESCAPE to leave rating submit mode.4 stars selected. Press SHIFT+ENTER to submit. Press TAB to increase rating. Press SHIFT+TAB to decrease rating. Press SHIFT+ESCAPE to leave rating submit mode.5 stars selected. Press SHIFT+ENTER to submit. Press SHIFT+TAB to decrease rating. Press SHIFT+ESCAPE to leave rating submit mode.
Categories: 
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!
Screenshot of the Calculated Column Formula Generator

Comments

Jason

SharePoint Calculated Column formula generator

this is going to be very helpful, thank you!

Posted 21-Apr-2009 by Jason
Frank

SharePoint Calculated Column formula generator

Once I pick the colors, how do I add the formula to the site? Will this work with WSS 3.0?
Thanks

Posted 21-Apr-2009 by Frank
EndUserSharePoint

SharePoint Calculated Column formula generator

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

Posted 21-Apr-2009 by EndUserSharePoint
Robin Majumdar

SharePoint Calculated Column formula generator

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

Posted 21-Apr-2009 by Robin Majumdar
EndUserSharePoint

SharePoint Calculated Column formula generator

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

Posted 21-Apr-2009 by EndUserSharePoint
Robin Majumdar

SharePoint Calculated Column formula generator

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

Posted 21-Apr-2009 by Robin Majumdar
EndUserSharePoint

SharePoint Calculated Column formula generator

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

Posted 21-Apr-2009 by EndUserSharePoint
links for 2009-04-21 | Alpesh Nakar Blogs on SharePoint, Microsoft and that's IT

SharePoint Calculated Column formula generator

[...] SharePoint Calculated Column formula generator | End User SharePoint RT @EUSP: New blog post: SharePoint Calculated Column formula generator http://www.endusersharepoint.com/?p=1542 (via @gannotti) [from http://twitter.com/alpesh/statuses/1578519596] (tags: tweecious Microsoft SharePoint Twitter XML HTML JQuery XHTML RSS Document Management) [...]

Posted 22-Apr-2009 by links for 2009-04-21 | Alpesh Nakar Blogs on SharePoint, Microsoft and that's IT
Robin Majumdar

SharePoint Calculated Column formula generator

Wow - thanks Mark, I'll give that code a try tomorrow and get back to you. That FIND operator sure is powerful!

Posted 22-Apr-2009 by Robin Majumdar
David Milliken

SharePoint Calculated Column formula generator

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.

Posted 04-May-2009 by David Milliken
EndUserSharePoint

SharePoint Calculated Column formula generator

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 :-)

Posted 04-May-2009 by EndUserSharePoint
EndUserSharePoint

SharePoint Calculated Column formula generator

... 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

Posted 04-May-2009 by EndUserSharePoint
David Milliken

SharePoint Calculated Column formula generator

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: ="

Again, I realize you're very busy -- your assistance is truly appreciated.

Thank You

Posted 04-May-2009 by David Milliken
EndUserSharePoint

SharePoint Calculated Column formula generator

> 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

Posted 04-May-2009 by EndUserSharePoint
Christophe

SharePoint Calculated Column formula generator

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.).

Posted 05-May-2009 by Christophe
EndUserSharePoint

SharePoint Calculated Column formula generator

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

Posted 05-May-2009 by EndUserSharePoint
Christophe

SharePoint Calculated Column formula generator

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.

Posted 05-May-2009 by Christophe
David Milliken

SharePoint Calculated Column formula generator

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?

Posted 05-May-2009 by David Milliken

Notify me of comments to this article

E-mail:
   

Add Comment

Title:

 
Comment:
Email:

   


Name:

 
Url: