Formula to identify current approver
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
-
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."))))))))
-
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.
-
That worked perfectly, thank you!
Answers
-
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"))
-
@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")?
-
Sorry, I said "approved, in my example, but should have been "Pending." My apologies.
-
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.
-
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."))))))))
-
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."))))))))
-
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!
-
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?
-
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.
-
That worked perfectly, thank you!
Help Article Resources
Categories
Check out the Formula Handbook template!
