Approval Formula Needed

MJ215

Hi All,

I have an approval process that keeps evolving. Right now if the user selects they are representing an AVG the approval goes to the AVG first. The current formula I have looks to see if the AVG Approval field is not blank then if it is not blank it looks for the "Approved" selection. Now I will need to add multiple AVG option. Could I have AVG 1, AVG 2, AVG 3 columns added to this formula and if AVG 3 is blank only check for AVG 1 and AVG 2 approval and then check the additional approvals? And if AVG 2 and AVG 3 are blank only look for AVG 1 approval then Jim and Angela.

Current Formula:

=IF(ISBLANK([AVG VP Approval-RD]@row), IF(COUNTIF([Jim Approval-RD]@row:[Angela Approval-RD]@row, "Approved") = 2, "All Approved", IF(CONTAINS("Declined", [Jim Approval-RD]@row:[Angela Approval-RD]@row), "Request Declined", IF(CONTAINS("Pending", [Jim Approval-RD]@row:[Angela Approval-RD]@row), "Pending Approval"))), IF(COUNTIF([AVG VP Approval-RD]@row:[Angela Approval-RD]@row, "Approved") = 3, "All Approved", IF(CONTAINS("Declined", [AVG VP Approval-RD]@row:[Angela Approval-RD]@row), "Request Declined", IF(CONTAINS("Pending", [AVG VP Approval-RD]@row:[Angela Approval-RD]@row), "Pending Approval"))))

Mark Cronk

Hi @MJ215 ,

Add your AVG VP 1, 2 and 3 columns. Then try formula below. I broke it into pieces to make it easier to follow. Put it back together for use:

= IF(CONTAINS("Declined", [AVG VP1 Approval-RD]@row:[Angela Approval-RD]@row), "Request Declined",

IF(CONTAINS("Pending", [AVG VP1 Approval-RD]@row:[Angela Approval-RD]@row), "Pending Approval",

IF(ISTEXT([AVG VP1 Approval-RD]@Row),

IF(COUNTIF(AVG VP1 Approval-RD]@row:[Angela Approval-RD]@row, "Approved")=5, "All Approved",

IF(IF(ISTEXT([AVG VP2 Approval-RD]@Row),

IF(COUNTIF(AVG VP2 Approval-RD]@row:[Angela Approval-RD]@row, "Approved")=4, "All Approved",

IF(ISTEXT([AVG VP3 Approval-RD]@Row),

IF(COUNTIF(AVG VP3 Approval-RD]@row:[Angela Approval-RD]@row, "Approved")=3, "All Approved",

IF(COUNTIF([Jim approval-rd]@row:[Angela Approval-RD]@row, "Approved")=2, "All Approved", "Pending Approval")))))))))

Work?

Mark

MJ215

No, my first attempt at this I am getting an #incorrect argument set error.

=IF(CONTAINS("Declined", [AVG VP1 Approval-RD]@row:[Angela Approval-RD]@row), "Request Declined", IF(CONTAINS("Pending", [AVG VP1 Approval-RD]@row:[Angela Approval-RD]@row, "Pending Approval", IF(ISTEXT([AVG VP1 Approval-RD]@row), IF(COUNTIF([AVG VP1 Approval-RD]@row:[Angela Approval-RD]@row, "Approved") = 5, "All Approved", IF(IF(ISTEXT([AVG 2 VP Approval]@row, IF(COUNTIF([AVG 2 VP Approval]@row:[Angela Approval-RD]@row, "Approved") = 4, "All Approved", IF(ISTEXT([AVG 3 VP Approval]@row), IF(COUNTIF([AVG 3 VP Approval]@row:[Angela Approval-RD]@row, "Approved") = 3, "All Approved", IF(COUNTIF([Jim Approval-RD]@row:[Angela Approval-RD]@row, "Approved") = 2, "All Approved", "Pending Approval")))))))))

Mark Cronk

If I'm understanding your sheet correctly, if Pending or Declined doesn't exist than it is approved. If so, try:

=IF(CONTAINS("Declined", [AVG VP1 Approval-RD]@row:[Angela Approval-RD]@row), "Request Declined", IF(CONTAINS("Pending", [AVG VP1 Approval-RD]@row:[Angela Approval-RD]@row, "Pending Approval", "All Approved"))

Mark

MJ215

That doesn't seem to work either. Basically if the user selects they are representing 3 VPs they would need the approval from 3 VPs then the final 2 folks. But if they only represent 1 VP they would just need approval from 1 AVG VP then the final 2 folks.

My business requirements keep updating, so it looks like I will need to scale this to 7 possible AVG VPs, so the formula would need to check to see if all AVG VPs have approved the request and that could be 1 AVG VP or 7 AVG VPs.