Creating a status using project stage

Erica Cole · 08/16/22

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 · 08/16/22

Hey @Erica Cole

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

Erica Cole · 08/16/22

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 · 08/16/22

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

Creating a status using project stage

Answers

Help Article Resources

Categories