Welcome to the Smartsheet Forum Archives

The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

RYGB Balls

Shesha_K ✭✭✭
edited 12/09/19 in Archived 2017 Posts

I created a nested function to color designate the status of my projects tasks:

=IF(Status="Not Started","Red", =IF(Status="In Progress","Yellow",=IF(Status="Complete", "Green",=IF(Status="N/A","Blue)))) 

The Red, Yellow, and Green balls work perfectly, however, when I select N/A from the drop down in the cell I get #INVALID OPERATION error message.  Is there another way to write this function?  

Is it not possible to automate the Blue ball?



  • Shesha_K
    Shesha_K ✭✭✭

    Even the ability to leave that column blank(No ball) would be an option.

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    Hello. You're missing a closing quotation mark after Blue.

    Try this:  =IF(Status="Not Started","Red", =IF(Status="In Progress","Yellow",=IF(Status="Complete", "Green",=IF(Status="N/A","Blue")))) 

  • Shesha_K
    Shesha_K ✭✭✭

    Ugh! Thank you!  I guess I just kept overlooking that!

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    Easily missed! What clued me into it was that it was only failing on the blue. 

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭

    Are you messing with the newbies? I can not believe it was "only failing on the blue"

    =IF(Status="Not Started","Red", =IF(Status="In Progress","Yellow",=IF(Status="Complete", "Green",=IF(Status="N/A","Blue"))))

    This formula will not work.

    You don't have a cell reference and you have =IF not IF in the nest.

    This formula will work for row 13

    =IF(Status13 = "Not Started", "Red", IF(Status13 = "In Progress", "Yellow", IF(Status13 = "Complete", "Green", IF(Status13 = "N/A", "Blue"))))

    Newbies may try to copy your formula exactly and have no idea what they are doing wrong.



  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    Oh my goodness! You are correct Craig. I totally overlooked the missing row numbers. When I said it was failing on the blue, I meant from her picture. She stated it would pass sometimes but not always. I completely concur with Craig - you must have row numbers in there to make the formula work. 

This discussion has been closed.