RYG Balls: Using IF/OR Function to Create Stoplight Status for Different Word Values

NMC AH

Hi,

I'm trying to create a formula that shows stoplight status (red, yellow, green) for different statuses that are word values. I would like one color to correspond to several statuses. See below. I've also included the formula I though would work using IF/OR but I'm receiving the "Incorrect Argument" message. Any help is appreciated.

(Red Dot) would represent that the recruitment process was in the “requested, hold, pending with comment, pending proforma, hold” status.

(Yellow Dot) would represent that the recruitment process was in the “ready to launch, looking, interviewing” status.

(Green Dot) would represent that the recruitment process was in the “LOI sent, LOI signed, contract sent, contract signed” status.

=IF((OR([Recruitment Status]3 = "Requested", [Recruitment Status]3 = "Hold", [Recruitment Status]3 = "Red", IF([Recruitment Status]3 = "Pending With Comment", [Recruitment Status]3 = "Pending Proforma", "Red", IF(OR([Recruitment Status]3 = "Ready to Launch", [Recruitment Status]3 = "Looking", [Recruitment Status]3 = "Interviewing", "Yellow", "Green"))))))

Thanks!

NMC AH

ron.judenberg112096

=IF(OR([Recruitment Status]@ROW = "Requested", [Recruitment Status]@ROW = "Hold", [Recruitment Status]@ROW = "Pending With Comment", [Recruitment Status]@ROW = "Pending Proforma"), "Red", IF(OR([Recruitment Status]@ROW = "Ready to Launch", [Recruitment Status]@ROW = "Looking", [Recruitment Status]@ROW = "Interviewing"), "Yellow", "Green"))

This assumes any status not listed above should yield green

Also, note the use of @ROW. This will prevent issues when you copy the function to successive rows.

Paul Newcome

@NMC AH & @ron.judenberg112096

I just wanted to chime in about "@ROW". One thing to keep in mind is that it must be lower cased.

"@ROW" will throw the #UNPARSEABLE error. You will want to use "@row" in all lower case.

Using an @row reference also assumes that all fields using that reference are on the same row as the formula. If the formula is on row 3 but you want to reference row 4, you will need to specify that instead of using the @row reference.

NMC AH

Thanks @ron.judenberg112096 and @Paul Newcome ! I had a few errors in my original formula. It's always good to have separate eyes review. Thanks so much for your help!