Skip to content
  • Try Smartsheet

    • Formulas and Functions

    Formulas and Functions

    Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

    What is wrong with this formula?

    ✭✭✭✭
    in Formulas and Functions
    image.png

    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

    image.png
    · Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    Best Answer

    • Community Champion
      Answer ✓

      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

      · Share on FacebookShare on Twitter

    Answers

    • ✭✭✭✭✭

      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")

      image.png
      · Share on FacebookShare on Twitter
    • ✭✭✭✭

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

      image.png
      · Share on FacebookShare on Twitter
    • Community Champion
      Answer ✓

      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

      · Share on FacebookShare on Twitter
    • ✭✭✭✭

      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! :)

      image.png
      · Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    Help Article Resources

    Create and edit formulas in Smartsheet

    Formula combinations for cross sheet references

    Smartsheet functions list

    Quick Links

    Categories

    Want to practice working with formulas directly in Smartsheet?

    Check out the Formula Handbook template!

    Trending in Formulas and Functions