What is wrong with this formula?

Kim Shaddix

My goal is for the Approvals for Reporting column to populate with either Pending or Approved, based on what is in the 3 columns prior to it - it is only populating correctly if the ICRA Only column is Approved - otherwise I get #INCORRECT ARGUMENT

The formula is:

=IF([ICRA Only Approved Column]@row = "Approved", "Approved", IF(OR([ALSM only Approved Column]@row = "Approved", "Approved", IF(OR([All Other Approvals]@row = "Approved"), "Approved", "Pending"))))

Please advise! Thanks

Kelly Moore

Hey @Kim Shaddix

I wasn't certain if your criteria meant if any of the approvals were Pending to show Pending, or to do the Approvals from left to right and whatever the non-blank right-most approval says is the status. The disadvantage of that is wouldn't know what status you were reflecting and it could be overturned at the next step. This assumes you could have multiple responses in the same row - which from your screenshot I'm not sure is a concern.

Assuming you want if any step is Pending, show Pending, then try this approach:

=IF(COUNTIFS([ICRA Only Approved Column]@row:[All Other Approvals]@row,<>"")>0,IF(COUNTIFS([ICRA Only Approved Column]@row:[All Other Approvals]@row, "Pending")>0, "Pending", "Approved"))

This counts across the row to see if, first there is anything in the row. Otherwise the row will remain blank. The next IF checks for the count of "Pending", which would give a count greater than zero if found. If the count is greater than zero, show Pending, otherwise show Approved.

Will this approach work for you?
Kelly

dojones

Try this formula

=IF([ICRA Only Approved Column]@row = "Approved", IF([ALSM only Approved Column]@row = "Approved", IF([All Other Approvals]@row = "Approved", "Approved", "Pending"), "Pending"), "Pending")

Kim Shaddix

Thanks - but it didn't quite work. Now they all show Pending.

Kelly Moore

Hey @Kim Shaddix

I wasn't certain if your criteria meant if any of the approvals were Pending to show Pending, or to do the Approvals from left to right and whatever the non-blank right-most approval says is the status. The disadvantage of that is wouldn't know what status you were reflecting and it could be overturned at the next step. This assumes you could have multiple responses in the same row - which from your screenshot I'm not sure is a concern.

Assuming you want if any step is Pending, show Pending, then try this approach:

=IF(COUNTIFS([ICRA Only Approved Column]@row:[All Other Approvals]@row,<>"")>0,IF(COUNTIFS([ICRA Only Approved Column]@row:[All Other Approvals]@row, "Pending")>0, "Pending", "Approved"))

This counts across the row to see if, first there is anything in the row. Otherwise the row will remain blank. The next IF checks for the count of "Pending", which would give a count greater than zero if found. If the count is greater than zero, show Pending, otherwise show Approved.

Will this approach work for you?
Kelly

Kim Shaddix

Kelly - thank you so much!!! That is perfect - and does exactly what I needed it to do. Really appreciate your taking the time to help me! :)