How to Find the Furthest Status

Options
Erica Cole
Erica Cole ✭✭✭✭
edited 06/28/22 in Formulas and Functions

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.

Tags:

Best Answer

  • Mike TV
    Mike TV ✭✭✭✭✭✭
    Answer ✓
    Options

    @Erica Cole

    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

  • Mike TV
    Mike TV ✭✭✭✭✭✭
    Answer ✓
    Options

    @Erica Cole

    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)))

  • Erica Cole
    Erica Cole ✭✭✭✭
    Options

    @Mike TV


    Thank you so much this is an awesome solution :)

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!