Formula to identify current approver

Options

Good morning,

I have a worksheet with a multi-approver workflow on it. Upper Management wants a report that shows who is the current approver without having to see all of the previous approvers.

Does anyone know, is there a way to construct a formula that would identify which approver the request currently resides with?

The structure of the sheet is something like this: https://app.smartsheet.com/b/publish?EQBCT=a28a7318e8d543d99dd7f8332a48662d

Any and all thoughts are welcome. Thank you for the help.

Best Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓
    Options

    It looks like when you copy/pasted for the OR bit you grabbed the "=" before the IF.


    =IF([One-Up Manager Approval US]@row = "Pending", "US One Up", IF([VP One Up approval CA]@row = "Pending", "VP One Up CA", IF([VP Approval US]@row = "Pending", "VP US", IF(OR([HR Approval]@row = "Pending", [HR Approval]@row = "Under Review"), "HR", IF([Chris Gregory Approval]@row = "Pending", "Chris Gregory", IF([JK Milliken Approval]@row = "Pending", "JK Milliken", IF([Jamie Brink Approval]@row = "Pending", "Jaime Brink", IF([Paul H. Approval]@row = "Pending", "Paul H."))))))))

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓
    Options

    The easiest way of doing this would be to use something along the lines of...

    =MAX([1st Approval Date]@row, [2nd Approval Date]@row, [3rd Approval Date]@row)


    If it hasn't been approved then there won't be a date and blanks will be considered less than non-blanks which means they won't get pulled by the MAX function.

  • Kelly Ospina
    Kelly Ospina ✭✭✭✭✭
    Answer ✓
    Options

    That worked perfectly, thank you!

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    Do you basically want to pull the "Pending"? If so, it would look something like this...

    =IF([Controller Approval]@row = "Pending", "Controller", IF([VP Approval]@row = "Pending", "VP", "Manager"))

  • Kelly Ospina
    Kelly Ospina ✭✭✭✭✭
    Options

    @Paul Newcome that worked wonderfully!

    One more quesiton, though - in a particular column (HR Approval) there are two choices - "Under Review" and "Approved." In that instance, either of those responses would produce a result of "HR." How would I structure that one? Would I use OR? =IF([HR Approval]@row = "Pending"OR(IF([HR Approval]@row = "Under Review", "HR")?

  • Kelly Ospina
    Kelly Ospina ✭✭✭✭✭
    Options

    Sorry, I said "approved, in my example, but should have been "Pending." My apologies.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    It would look something like this...


    =IF(OR([HR Approval]@row = "Pending", [HR Approval]@row = "Under Review"), "HR", IF([Controller Approval]@row = "Pending", "Controller", IF([VP Approval]@row = "Pending", "VP", "Manager"))


    Of course you would want to nest it wherever it belongs to keep the sequence, but the above is an example of how to incorporate the OR statement. Essentially you drop your cell/criteria sets in the OR function separated by commas, then you take the entire OR function and drop it into the first portion of the IF statement.

  • Kelly Ospina
    Kelly Ospina ✭✭✭✭✭
    Options

    Ok, I see. Thank you, that makes more sense.

    I'm still getting an error message, though. I'm not sure what I did wrong here?

    =IF([One-Up Manager Approval US]@row = "Pending", "US One Up", IF([VP One Up approval CA]@row = "Pending", "VP One Up CA", IF([VP Approval US]@row = "Pending", "VP US", =IF(OR([HR Approval]@row = "Pending", [HR Approval]@row = "Under Review"), "HR", IF([Chris Gregory Approval]@row = "Pending", "Chris Gregory", IF([JK Milliken Approval]@row = "Pending", "JK Milliken", IF([Jamie Brink Approval]@row = "Pending", "Jaime Brink", IF([Paul H. Approval]@row = "Pending", "Paul H."))))))))

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓
    Options

    It looks like when you copy/pasted for the OR bit you grabbed the "=" before the IF.


    =IF([One-Up Manager Approval US]@row = "Pending", "US One Up", IF([VP One Up approval CA]@row = "Pending", "VP One Up CA", IF([VP Approval US]@row = "Pending", "VP US", IF(OR([HR Approval]@row = "Pending", [HR Approval]@row = "Under Review"), "HR", IF([Chris Gregory Approval]@row = "Pending", "Chris Gregory", IF([JK Milliken Approval]@row = "Pending", "JK Milliken", IF([Jamie Brink Approval]@row = "Pending", "Jaime Brink", IF([Paul H. Approval]@row = "Pending", "Paul H."))))))))

  • Kelly Ospina
    Kelly Ospina ✭✭✭✭✭
    Options

    I was just coming back to tell you I spotted the problem and fixed it!

    Thank you again for all of your help today. It is so very appreciated!

  • Kelly Ospina
    Kelly Ospina ✭✭✭✭✭
    Options

    I have a part two on this question. My boss has asked for a report that shows the current approver, which my earlier question covered. That's working great. But now she wants to see the date of the previous approval, so she can see how long it has been sitting with the current reviewer.

    I do have columns recording the approval date for each approver, but I don't know how to structure this formula. I'm not even sure this is doable.

    Does anyone have any suggestions?

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓
    Options

    The easiest way of doing this would be to use something along the lines of...

    =MAX([1st Approval Date]@row, [2nd Approval Date]@row, [3rd Approval Date]@row)


    If it hasn't been approved then there won't be a date and blanks will be considered less than non-blanks which means they won't get pulled by the MAX function.

  • Kelly Ospina
    Kelly Ospina ✭✭✭✭✭
    Answer ✓
    Options

    That worked perfectly, thank you!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!