#### 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

Options
✭✭✭
edited 12/09/19

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?

• ✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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"))))

• ✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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

• Options
• ✭✭✭✭✭✭
Options

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.

Craig

• ✭✭✭✭✭✭
Options

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.