Creating a status using project stage
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"))))))
Answers
-
Hey @Erica Cole
How is the number in any of the relevant columns being generated?
-
It's part of the source data. There are columns for each status the numbers of applicants for each status.
-
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
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 430 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!