Forum Discussion

Teresa145's avatar
Teresa145
Copper Contributor
Oct 20, 2023

SharePoint Choice Column Conditional Formatting

Hello everyone and thank you in advance for your help! 🙂

This is my List on SharePoint:

As we can see, there are different colors in the Status column, gray and green.

 

I used conditional formatting to have the green status band appear only if all the tasks are completed (you can observe that all the toggles in the second band of the next columns have been selected).

 

However, this is not exactly what I initially intended to do.

 

My goal is to create a Status Choice column with two options: "Completed" and "Not completed". These conditions depend, as before, on the tasks. If all the tasks have been completed, it should automatically change to "Completed"; otherwise, it should be set to "Not Completed".

I have already set up the Status as a choice column. However, up to this point, I can only manually add my choice preferences 😞 I would prefer it to be automatic.

 

Do you have ideas? Or YouTube tutorial about it? My research gave me no result. 

I´m a beginner, sorry if it sounds corny :flushed:

  • Rob_Elliott's avatar
    Rob_Elliott
    Bronze Contributor

    Teresa145 you can't do this with a choice column, it has to be a calculated column. In your list click add column then scroll down and select See all column types. That will take you to the create column screen where you enter the column name and select Calculated (calculation based on other columns).

     

    Your formula should be as follows (my Yes/No columns are called named Tickets, Bags, Transport and Hotel):

    =IF(AND(Tickets=TRUE,Bags=TRUE,Transport=TRUE,Hotel=TRUE),"Completed","Not Completed")

     

     

    This is a bit more advanced, but in case you were wondering, each of my Yes/No columns are formatted in advanced mode with the following JSON and the user clicks the toggle to select Yes or No:

    {
      "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
      "elmType": "div",
      "style": {
        "display": "flex",
        "width": "100%",
        "height": "100%",
        "align-items": "center"
      },
      "children": [
        {
          "elmType": "div",
          "style": {
            "display": "flex",
            "width": "60px",
            "height": "30px",
            "border-radius": "15px",
            "align-items": "center",
            "flex-direction": "row",
            "justify-content": "=if(@currentField , 'flex-end' , 'flex-start')",
            "cursor": "pointer"
          },
          "customRowAction": {
            "action": "setValue",
            "actionInput": {
              "YesNoColumnInternalName": "=if(@currentField , '0' , '1' )"
            }
          },
          "attributes": {
            "class": "=if(@currentField , 'ms-bgColor-greenLight' , 'ms-bgColor-blueLight')"
          },
          "children": [
            {
              "elmType": "div",
              "style": {
                "width": "18px",
                "height": "18px",
                "margin-left": "6px",
                "margin-right": "6px",
                "border-radius": "50%"
              },
              "attributes": {
                "class": "ms-bgColor-white"
              }
            }
          ]
        }
      ],
      "inlineEditField": "@currentField"
    }

     

    Rob
    Los Gallardos
    Microsoft Power Automate Community Super User.
    Principal Consultant, SharePoint and Power Platform WSP UK (and classic 1967 Morris Traveller driver)

Resources