Formula to identify current approver

Kelly Ospina

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.

Paul Newcome

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

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

That worked perfectly, thank you!

Paul Newcome

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

@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

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

Paul Newcome

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

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

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

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!

Paul Newcome

Happy to help. 👍️

Kelly Ospina

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

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

That worked perfectly, thank you!