How to Find the Furthest Status
I have 2 sheets. 1 with all my active job orders, 1 with all active job applications.
I want to create a column on the job report to represent the furthest active application
Example
I have a job called "Accountant" on the Job Order sheet. The ID# is 1234
On the application report there are five applications with a Job ID of 1234. Three are in a status of "New", one is "Interviewing" and one is "Offer".
How can I create a formula that pulls in the status of the furthest application to my job order sheet for each JobID? In this case the Account job order would have a "furthest Status" of "Offer"
I've decided the weight of each status. I'll likely need to create a column on the application sheet to convert the status to it's assigned weight using an IF statement. But don't know where to go from there.
Best Answer
-
Using a weighted status isn't necessary. I got it working another way.
Here's an example of sheet 1 where you collect information from:
Here's an example of sheet 2 where you compile information onto:
You only need to enter the Job ID into this 2nd sheet. It will pull in everything else.
Job Type formula:
=INDEX({Job Type}, MATCH([Job ID]@row, {Job ID}, 0))
Status formula (formula starts with the "furthest status" first and then down to the lowest):
=IFERROR(INDEX(COLLECT({Status}, {Job ID}, =[Job ID]@row, {Status}, ="Offer"), 1), IFERROR(INDEX(COLLECT({Status}, {Job ID}, =[Job ID]@row, {Status}, ="Interviewing"), 1), INDEX(COLLECT({Status}, {Job ID}, =[Job ID]@row, {Status}, ="New"), 1)))
Full Name formula (same with starting from the "furthest status" type first):
=IFERROR(INDEX(COLLECT({Full Name}, {Job ID}, =[Job ID]@row, {Status}, ="Offer"), 1), IFERROR(INDEX(COLLECT({Full Name}, {Job ID}, =[Job ID]@row, {Status}, ="Interviewing"), 1), INDEX(COLLECT({Full Name}, {Job ID}, =[Job ID]@row, {Status}, ="New"), 1)))
Answers
-
Using a weighted status isn't necessary. I got it working another way.
Here's an example of sheet 1 where you collect information from:
Here's an example of sheet 2 where you compile information onto:
You only need to enter the Job ID into this 2nd sheet. It will pull in everything else.
Job Type formula:
=INDEX({Job Type}, MATCH([Job ID]@row, {Job ID}, 0))
Status formula (formula starts with the "furthest status" first and then down to the lowest):
=IFERROR(INDEX(COLLECT({Status}, {Job ID}, =[Job ID]@row, {Status}, ="Offer"), 1), IFERROR(INDEX(COLLECT({Status}, {Job ID}, =[Job ID]@row, {Status}, ="Interviewing"), 1), INDEX(COLLECT({Status}, {Job ID}, =[Job ID]@row, {Status}, ="New"), 1)))
Full Name formula (same with starting from the "furthest status" type first):
=IFERROR(INDEX(COLLECT({Full Name}, {Job ID}, =[Job ID]@row, {Status}, ="Offer"), 1), IFERROR(INDEX(COLLECT({Full Name}, {Job ID}, =[Job ID]@row, {Status}, ="Interviewing"), 1), INDEX(COLLECT({Full Name}, {Job ID}, =[Job ID]@row, {Status}, ="New"), 1)))
-
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 379 Global Discussions
- 210 Industry Talk
- 441 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 300 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!