Skip to main content

Hi everyone, two part question. Can I identify based on the color of the single-select option, and can I display the field name as output from a formula?


I have a project tracking sheet with ~10 task fields, and a color coded dropdown status for each. From left to right, they will basically go from red to green, with in-progress colors in between. Was wondering if it’s possible to report the first column that matches a criteria (ex: not red or green) and have it appear in the status formula cell with the field name (ex: Fee Draft - Landlord Reviewing).


Here’s a hack I came up with for a sample 3 task using nested if’s, but I don’t want to have to resort to using emojis if I don’t need to, and don’t want to have to update if field names change.


Sample status:

Fee Form - Not drafted ❌ / Drafted/sent 👌 / ✅ Final sent ✅


IF(FIND(“ 👌 ”,{RFP Draft}),CONCATENATE("RFP Draft - ",{RFP Draft}),

IF(FIND(“ 👌 ”,{Fee Draft}),CONCATENATE("Fee Draft - ",{Fee Draft}),

IF(FIND(“ 👌 ”,{Fee Form}),CONCATENATE("Fee Form - ",{Fee Form}),

IF(FIND(“ ✅ ”,{Fee Form}),CONCATENATE(“Project Complete!”),

“”))))


Any other ideas to handle automating status updates?


Thanks in advance!

You cannot reference the color of a single-select option in a formula.

You cannot output the field name in an automated way from a formula.


However, a long and hairy enough nested conditional statement can do many, many things!


You’ll probably need to use more conditional logic within your IF() statements to achieve what you want - for example, OR() and AND():

For the sake of getting you a quick answer, here’s an example formula I use to produce a status field:


IF(

  {Missing?}=TRUE(),

  "🔴 Missing",

  IF(

    AND(

      {Picked Up}=FALSE(),

      {Dropped Off}=FALSE(),

      Stripped=FALSE()

    ),

    "⚪️ On Job Site",

    IF(

      AND(

        {Picked Up}=TRUE(),

        {Dropped Off}=FALSE(),

        Stripped=FALSE()

      ),

      "⚫️ Picked Up",

      IF(

        AND(

          {Picked Up}=TRUE(),

          {Dropped Off}=TRUE(),

          Stripped=FALSE()

        ),

        "🔵 Delivered",

        IF(

          AND(

            {Picked Up}=TRUE(),

            {Dropped Off}=TRUE(),

            Stripped=TRUE(),

            {UnProcessed Samples}!=0

          ),

          "🔘 Stripped/Marked",

          IF(

            AND(

              {Picked Up}=TRUE(),

              {Dropped Off}=TRUE(),

              Stripped=TRUE(),

              {UnProcessed Samples}!=0

            ),

            "☑️ Processed",

            "🔶 Sequence Error"

          )

        )

      )

    )

  )

)

The AND() statement requires that each comma-separated statement within it evaluates to “True” in order to pass as “True” for the IF() statement. The OR() statement requires that at least one of the comma-separated statements within it evaluates to “True” in order to pass as “True” for the IF() statement. You can use these two in combination to produce variable statuses based on any number of other fields and their conditions.


You’ll need to produce a conditional output for every possible combination of each dropdown field you have, I think.

You cannot reference the color of a single-select option in a formula.

You cannot output the field name in an automated way from a formula.


However, a long and hairy enough nested conditional statement can do many, many things!


You’ll probably need to use more conditional logic within your IF() statements to achieve what you want - for example, OR() and AND():

For the sake of getting you a quick answer, here’s an example formula I use to produce a status field:


IF(

  {Missing?}=TRUE(),

  "🔴 Missing",

  IF(

    AND(

      {Picked Up}=FALSE(),

      {Dropped Off}=FALSE(),

      Stripped=FALSE()

    ),

    "⚪️ On Job Site",

    IF(

      AND(

        {Picked Up}=TRUE(),

        {Dropped Off}=FALSE(),

        Stripped=FALSE()

      ),

      "⚫️ Picked Up",

      IF(

        AND(

          {Picked Up}=TRUE(),

          {Dropped Off}=TRUE(),

          Stripped=FALSE()

        ),

        "🔵 Delivered",

        IF(

          AND(

            {Picked Up}=TRUE(),

            {Dropped Off}=TRUE(),

            Stripped=TRUE(),

            {UnProcessed Samples}!=0

          ),

          "🔘 Stripped/Marked",

          IF(

            AND(

              {Picked Up}=TRUE(),

              {Dropped Off}=TRUE(),

              Stripped=TRUE(),

              {UnProcessed Samples}!=0

            ),

            "☑️ Processed",

            "🔶 Sequence Error"

          )

        )

      )

    )

  )

)

The AND() statement requires that each comma-separated statement within it evaluates to “True” in order to pass as “True” for the IF() statement. The OR() statement requires that at least one of the comma-separated statements within it evaluates to “True” in order to pass as “True” for the IF() statement. You can use these two in combination to produce variable statuses based on any number of other fields and their conditions.


You’ll need to produce a conditional output for every possible combination of each dropdown field you have, I think.


Thanks Jeremy, that’s what I needed to know. I like the logic, I hadn’t thought of a good way to test for sequence errors, and this seems to nail it. You’ve also given me the idea of swapping out the emojis I was using for an icon like ⚪ ️ or > that I can use for whatever is considered "in progress. Probably do that before I get to building the monstrous if statement since we’re still changing things around. Cheers.

Reply


Terms & Conditions