1

I have a task list which has color coding for Progress Bar and Task Status based on % Complete and Status column values. I have one more additional condition for: IF status=Blocked then whatever the % Complete value may be the Progress Bar background color should be Red.

I have following formula for Progress Bar:

="<DIV style='display:inline-block;position:relative; width:60px; height:17.5px;border:1px solid;'><DIV style='background-color:"&CHOOSE(INT([% Complete]*10)+1,"red","red","OrangeRed","OrangeRed","DarkOrange","Orange","Gold","yellow","GreenYellow","LawnGreen","Lime")&"; width:"&([% Complete]*100)&"%;'>&nbsp;</DIV><DIV style='position:absolute; top:0px;'>"&TEXT([% Complete],"0%")&"</DIV></DIV>"

Task Status:

="<span style=""font-weight:bold;color:"&IF(Status="Blocked","red",IF(Status="Completed","limegreen",IF(Status="In Progress","gold",IF(Status="Not Started","lightblue","lightslategray"))))&""">"&Status&"</span>"

Tried combining both unsuccessfully:

="<DIV style='display:inline-block;position:relative; width:60px; height:17.5px;border:1px solid;'><DIV style='background-color:"&IF(Status="Blocked","red","&CHOOSE(INT([% Complete]*10)+1,"red","red","OrangeRed","OrangeRed","DarkOrange","Orange","Gold","yellow","GreenYellow","LawnGreen","Lime")&"; width:"&([% Complete]*100)&"%;'>&nbsp;</DIV><DIV style='position:absolute; top:0px;'>"&TEXT([% Complete],"0%")&"</DIV></DIV>"

Any pointers to solve this issue? enter image description here

0

I ONLY copy/pasted your formula and only added linebreaks and spaces (which SharePoint ignores) in Notepad.

Looking at this:

There is a incorrect double quote at the end of the IF line

The rest seems fine by me.

="<DIV style='display:inline-block;position:relative;width:60px;height:17.5px;border:1px solid;'>"
  &"<DIV style='background-color:"
    &IF(Status="Blocked"
      ,"red"
      ,"   HERE IS YOUR TYPO, EITHER ADD A QUOTE OR DELETE THE QUOTE AND THE FOLLOWING &
        &CHOOSE(
          INT([% Complete]*10)+1
              ,"red","red","OrangeRed","OrangeRed","DarkOrange","Orange","Gold","yellow","GreenYellow","LawnGreen","Lime")
    &"; width:"
    &([% Complete]*100)
    &"%;'>&nbsp;</DIV><DIV style='position:absolute; top:0px;'>"
    &TEXT([% Complete],"0%")
  &"</DIV></DIV>"

You can also concatenate your 2 existing Calculated Columns to create a new one; then use that result in your view

Tip: always create complex formulas in Notepad, add linebreaks and spaces to make it readable; SharePoint will ignore them when you paste your formula.

  • the changes suggested by you still dont work. "The formula contains a syntax error or is not supported", this is what sharepoint says. – Amod Raje Feb 26 '15 at 5:59
  • Alas that error message is about the friendliest you are going to get working with Calculated Columns. Delete all stuff you know is correct, you will eventually find the error. I usually start with the minimal working version and built it step by step. I am not going to do this for you, you will learn from your own mistakes... count the number of double quotes.. should be even... count the number of opening ( and closing )... darn I helped you with this last tip ;-) – Danny '365CSI' Engelman Feb 26 '15 at 16:00
  • Before people start blaming me for sending you on a wild goose chase and start taking away my StackOverflow points. The problem is with the parameters of the IF statement, you allready saw the problematic " .. but if you take that away the next parameter CHOOSE starts with a & . I missed that in my original analysis.. HTH +1 – Danny '365CSI' Engelman Feb 26 '15 at 16:47
  • And don't forget the goose chase .. there is a closing ) for the IF missing.. – Danny '365CSI' Engelman Feb 26 '15 at 16:58
  • Appreciate all the pointers, will update when i am done with this puzzle :) – Amod Raje Feb 27 '15 at 5:35
0

I am able to achieve the colored background for progress bar based on task status. Used Status column instead of % Complete

="<DIV style='display:inline-block;position:relative;width:60px;height:17.5px;border:1px solid;'>"
&"<DIV style='background-color:"
&IF(Status="Blocked","red",
  IF(Status="Completed","limegreen",
  IF(Status="In Progress","gold",
  IF(Status="Deferred","gray",
  ))))
&"; width:"
&([% Complete]*100)
&"%;'>&nbsp;</DIV><DIV style='position:absolute; top:0px;'>"
&TEXT([% Complete],"0%")
&"</DIV></DIV>"

.....

  • great! If you think my answer was helpfull, you may mark it as an answer. tnx – Danny '365CSI' Engelman Feb 28 '15 at 10:45

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.