locked
SharePoint 2007 List - Calculated Column RRS feed

  • Question

  • I have a Priority column with three options: Low, Medium, High. I'm looking to use a formula in a calculated column to output a color based on the Priority column's selection. Example: Green for low, then yellow and red.

    I've found a few references online but so far none have seemed to work just yet.

    Any help is much appreciated!
    Wednesday, January 4, 2012 3:24 PM

Answers

  • Actually you are missing one more value in the last IF, and dont remove all = symbols. it should be like below

     

    =IF(Priority="Low","<DIV><img src URL1></DIV>",IF(Priority="Medium","<DIV><img src URL2></DIV>",IF(Priority="High","<DIV><img src URL3></DIV>", ""))


    Ram Prasad Meenavalli | MCTS SharePoint 2010 | MCPD SharePoint 2010

    Friday, January 6, 2012 6:39 PM
  • Looks like it's supposed to work that way actually. The site below says you use a CEWP below the list to make the text then display as html.

    http://sharepointpratik.blogspot.com/2011/06/add-image-to-custom-list-with_30.html

     

    Thx for suggesting about not using the = sign. Going to try it in a few minutes.

    Friday, January 6, 2012 2:32 PM

All replies

  • Hello,

    I am assuming that Priority  column is of type Single Line of Text.

    I created a single line of text column (Priority) and created a calculated column with the below formula

    =IF([Priority]="Low", "Green", (IF([Priority]="Medium", "Yellow", IF([Priority]="High", "Red", "Orange")))

    It worked fine.

    Hope this helps

     

     


    Ram Prasad Meenavalli | MCTS SharePoint 2010 | MCPD SharePoint 2010
    Wednesday, January 4, 2012 3:43 PM
  • That looks incredibly simple. I'm going to try that now, Ram. Thanks! I'll report back with my results.
    Wednesday, January 4, 2012 3:46 PM
  • It looks like it literally inserted the words for the colors. Was there a way using the calculated column to actually display color? Maybe Colored text or a colored background. That way instead of seeing Low, Medium, High, you actually see colors.
    Wednesday, January 4, 2012 4:10 PM
  • No its not possible using the Calculated columns. Infact you can do that using conditional formatting of list view webpart.

    You can check this link for the steps to be followed for MOSS 2007.


    Ram Prasad Meenavalli | MCTS SharePoint 2010 | MCPD SharePoint 2010

    • Edited by Mike Walsh FIN Wednesday, January 4, 2012 5:12 PM 2010 link replaced with 2007 link from later post (now deleted)
    Wednesday, January 4, 2012 4:16 PM
  • Another option is to upload three images, one for each priority.  Then for each item, assign the appropriate image. Display the image to the user in what ever view.
    Steve Clark, MCTS | Twin-Soft.com
    Wednesday, January 4, 2012 4:48 PM
  • @RamPrasad: I'm actually restricted to only options via the SP UI itself. No access to infopath, designer, or anything else. Just the default options.

     

    @SClark: That sounds like it could work. Do you by chance know what the new version of the below code would be if images were used? Obviously the URLs won't be specific, but I'm not super familiar with formatting and i'd be likely to make it not work by missing just a single comma or something heh.

    =IF([Priority]="Low", "Green", (IF([Priority]="Medium", "Yellow", IF([Priority]="High", "Red", "Orange")))

     

    Wednesday, January 4, 2012 5:02 PM
  • >@RamPrasad: I'm actually restricted to only options via the SP UI itself. No access to infopath, designer, or >anything else. Just the default options.

    As usual I am confused by the rejection of SPD 2007 which is both a free download and is a virtually essential tool for anyone wanting to administrate a WSS 3.0 / MOSS 2007 site.

    (Yet because it used to be a chargeable product is a fully supported and quality product.)

     


    SP 2010 "FAQ" (mainly useful links): http://wssv4faq.mindsharp.com/default.aspx
    WSS3/MOSS FAQ (FAQ and Links) http://wssv3faq.mindsharp.com/default.aspx
    Both also have links to extensive book lists and to (free) on-line chapters
    Wednesday, January 4, 2012 5:14 PM
  • @MikeWalsh: Ya the only thing I can think is they don't want tons of untrained people using a product that will eventually just raise more support questions and take up additional resources than the efforts are worth.

    @SClark/RamPrasad: Any thoughts on the exact calculated column formula to use if I want to just use the method of having an image displayed depending on each choice?

     

    Wednesday, January 4, 2012 5:50 PM
  • I just tried this formula:

     

    =IF(Priority="Low","<DIV><img src=URL1></DIV>",=IF(Priority="Medium","<DIV><img src=URL2></DIV>",=IF(Priority="High","<DIV><img src=URL3></DIV>"))

     

    but I receive this error: The formula contains a syntax error or is not supported.

     

    Notice anything that's off? I took the details from this site: http://sharepointpratik.blogspot.com/2011/06/add-image-to-custom-list-with_30.html but I must be doing something wrong.

    Wednesday, January 4, 2012 10:52 PM
  • Remove the = within the IF conditions.

    =IF(Priority="Low","<DIV><img src=URL1></DIV>",IF(Priority="Medium","<DIV><img src=URL2></DIV>",IF(Priority="High","<DIV><img src=URL3></DIV>"))

    But this wont work I guess. I tried with <img> tags, and in the list they were actually displayed as text, but not the image


    Ram Prasad Meenavalli | MCTS SharePoint 2010 | MCPD SharePoint 2010
    Thursday, January 5, 2012 4:15 AM
  • Looks like it's supposed to work that way actually. The site below says you use a CEWP below the list to make the text then display as html.

    http://sharepointpratik.blogspot.com/2011/06/add-image-to-custom-list-with_30.html

     

    Thx for suggesting about not using the = sign. Going to try it in a few minutes.

    Friday, January 6, 2012 2:32 PM
  • Just getting the same error when I remove the = sign. Should I replace it with something or just leave a space like below?

     

    IF(Priority "Low","<DIV><img src URL1></DIV>",IF(Priority "Medium","<DIV><img src URL2></DIV>",IF(Priority "High","<DIV><img src URL3></DIV>"))

    Friday, January 6, 2012 6:34 PM
  • Actually you are missing one more value in the last IF, and dont remove all = symbols. it should be like below

     

    =IF(Priority="Low","<DIV><img src URL1></DIV>",IF(Priority="Medium","<DIV><img src URL2></DIV>",IF(Priority="High","<DIV><img src URL3></DIV>", ""))


    Ram Prasad Meenavalli | MCTS SharePoint 2010 | MCPD SharePoint 2010

    Friday, January 6, 2012 6:39 PM
  • "As usual I am confused by the rejection of SPD 2007 which is both a free download and is a virtually essential tool for anyone wanting to administrate a WSS 3.0 / MOSS 2007 site."

    In the Gov't sector, it takes acts of Congress to get SPD on a machine. Mostly for the reasons that iherdon stated. 

    A little knowledge IS a dangerous thing. :D


    Steve Clark, MCTS | Twin-Soft.com
    Monday, January 9, 2012 4:12 PM