Creating a status using project stage

Erica Cole

My data is a list of jobs all having 8 applicant stages. These 8 stages will ultimately represent 6 statuses. I am looking for a formula that will let me assign a status to each job based on the 8 applicant stages. Because jobs can have new applicants and an applicant in the offer step, I need it to work from the furthest value to newest value. I put together the formula below, it has worked in a previous instance where the data it used were words, example IF(OR([ON-Live]@row = "New")... Im not sure if the issue is that now I'm counting numerical values instead of words and using >vs =. Any advice would be greatly appreciated :)

=IF(OR([ON-Live]@row >0, [BG-Live]@row >0), "Prehire Checks", IF(OR([OF-Live]@row >0), "Offer", IF(OR([H3-Live]@row >0, [H2-Live]@row>0), "Interviews", IF(OR([HM-Live]@row>0) "Submission to HM", IF(OR([PS-Live]@row>0) "Recruiter Screening", IF(OR([RS-Live]>0), "New"))))))

Kelly Moore

Hey @Erica Cole

How is the number in any of the relevant columns being generated?

Erica Cole

https://community.smartsheet.com/discussion/comment/339833#Comment_339833

It's part of the source data. There are columns for each status the numbers of applicants for each status.

Kelly Moore

I'm wondering if the characters are coming over as actual numbers or if smartsheet thinks they are text even though they look like numbers. Is it a formula that pulls them in? As a test, in the [ON-Live]@row formula, please wrap the formula in a VALUE function. It would be good to try this in a row where the result is greater than zero so it would trigger a color value in your RYG formula.

[ON-Live] test formula. Note the leading and closing parentheses you must add

=VALUE(your entire existing formula, including all the parentheses)

This will force smartsheet to treat the result as a number.

Did this work to create a color?

Kelly