How can I get my formula to return a phrase instead of #INVALID VALUE
I have a sheet that lists job openings. There is a column formula that assigns a "status" for each job by searching another sheet of applications. For example, if a job has 3 associates applications, and their statuses are "New" "Interviewing" and "Offer" it assigns that job a status of 'offer" because that is the furthest application. I use the formula below, and it works really well, except in cases where the requisition has not applications. It returns "#INVALID VALUE".
Is there a way to edit this formula so that if it finds no applications associated with the req# at the row, it will return the value "no applications"
=
IFERROR(INDEX(COLLECT({Status}, {Req Number}, =[Req Number]@row, {Status}, ="BG - Applicant Pool"), 1),
IFERROR(INDEX(COLLECT({Status}, {Req Number}, =[Req Number]@row, {Status}, ="BG - Form Completed"), 1),
IFERROR(INDEX(COLLECT({Status}, {Req Number}, =[Req Number]@row, {Status}, ="BG - Started"), 1),
IFERROR(INDEX(COLLECT({Status}, {Req Number}, =[Req Number]@row, {Status}, ="BG - Under Review"), 1),
IFERROR(INDEX(COLLECT({Status}, {Req Number}, =[Req Number]@row, {Status}, ="BG - In Process"), 1),
IFERROR(INDEX(COLLECT({Status}, {Req Number}, =[Req Number]@row, {Status}, ="OF - Accepted"), 1),
IFERROR(INDEX(COLLECT({Status}, {Req Number}, =[Req Number]@row, {Status}, ="OF - Extended"), 1),
IFERROR(INDEX(COLLECT({Status}, {Req Number}, =[Req Number]@row, {Status}, ="OF - Applicant Pool"), 1),
IFERROR(INDEX(COLLECT({Status}, {Req Number}, =[Req Number]@row, {Status}, ="OF - Extended"), 1),
IFERROR(INDEX(COLLECT({Status}, {Req Number}, =[Req Number]@row, {Status}, ="OF - Ready to Extend"), 1),
IFERROR(INDEX(COLLECT({Status}, {Req Number}, =[Req Number]@row, {Status}, ="OF - Pending"), 1),
IFERROR(INDEX(COLLECT({Status}, {Req Number}, =[Req Number]@row, {Status}, ="OF - Pending Approval"), 1),
IFERROR(INDEX(COLLECT({Status}, {Req Number}, =[Req Number]@row, {Status}, ="OF - Ready to Extend"), 1),
IFERROR(INDEX(COLLECT({Status}, {Req Number}, =[Req Number]@row, {Status}, ="HM - Interview Scheduled"), 1),
IFERROR(INDEX(COLLECT({Status}, {Req Number}, =[Req Number]@row, {Status}, ="HM - Eligible to Schedule"), 1),
IFERROR(INDEX(COLLECT({Status}, {Req Number}, =[Req Number]@row, {Status}, ="HM - Review"), 1),
IFERROR(INDEX(COLLECT({Status}, {Req Number}, =[Req Number]@row, {Status}, ="PS - Scheduled"), 1),
IFERROR(INDEX(COLLECT({Status}, {Req Number}, =[Req Number]@row, {Status}, ="PS - Eligible to Schedule"), 1),
IFERROR(INDEX(COLLECT({Status}, {Req Number}, =[Req Number]@row, {Status}, ="RS - Applicant Pool"), 1),
IFERROR(INDEX(COLLECT({Status}, {Req Number}, =[Req Number]@row, {Status}, ="AB - Incomplete"), 1),
IFERROR(INDEX(COLLECT({Status}, {Req Number}, =[Req Number]@row, {Status}, ="RM - Talent Community"), 1),
IFERROR(INDEX(COLLECT({Status}, {Req Number}, =[Req Number]@row, {Status}, ="RM - TalentPool"), 1),
IFERROR(INDEX(COLLECT({Status}, {Req Number}, =[Req Number]@row, {Status}, ="AP - Incomplete"), 1),
INDEX(COLLECT({Status}, {Req Number}, =[Req Number]@row, {Status}, ="AP - Applicant Pool"), 1))))))))))))))))))))))))
Best Answer
-
You would wrap the whole thing in one more IFERROR.
=IFERROR(.....................)))))))))))))))))))))))), "no applications")
Answers
-
I would add If({Status},"","phrase you want to appear",put the rest of your formula here). This is assuming your Status is blank when there is no application. If there is something there then replace "" with what is there.
-
Here's an idea that will eliminate that huge formula.
Create a helper sheet, call it Status Value. List each job status in ascending order, and assign a numeric value to each one in another column.
On your sheet of applications, create a hidden column called Status Level with a column formula INDEX/MATCH that pulls the numeric value for the status from your helper sheet. =INDEX({Status Value Sheet Number Column}, MATCH(Status@row), {Status Value Sheet Job Status Column}, 0))
By giving each status a numeric value that ascends according to the application timeline, you can then find the row with the highest status and level and collect its status text value. Back on your Requisition Status sheet, try replacing that super long formula with this one:
=IFERROR(INDEX(COLLECT({Status}, {Req Number}, =[Req Number]@row, {Status Level}, (MAX(COLLECT({Status Level}, {Req Number}, =[Req Number]@row))))), "No Applications")
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
You would wrap the whole thing in one more IFERROR.
=IFERROR(.....................)))))))))))))))))))))))), "no applications")
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 410 Global Discussions
- 219 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!