I have four cells that require approval, I want to create a formula

Options
Suzymae
Suzymae ✭
in Formulas and Functions

I have four cells that require approval, I want to create a formula that says if all four cells are blank then the cell for approval status is red. If all four cells are not blank then "Green" for approved.

I am a novice user and any help would be greatly appreciated.

Thanks,

· Share on FacebookShare on Twitter

Best Answer

  • Christina09
    Christina09 ✭✭✭✭✭✭
    Answer ✓
    Options

    @Suzymae

    Please see below screenshot for the formula

    image.png


    =IF(AND([Column1]@row = "", [Column2]@row = "", [Column3]@row = "",[Column4]@row = ""), "Red", IF(AND(NOT(ISBLANK([Column1]@row)), NOT(ISBLANK([Column2]@row)), NOT(ISBLANK([Column3]@row)), NOT(ISBLANK([Column4]@row))), "Green"))

    · Share on FacebookShare on Twitter

Answers

  • Christina09
    Christina09 ✭✭✭✭✭✭
    Options

    Hi @Suzymae

    You can use nested IF with AND function:

    =IF(AND([Column1]@row = "", [Column2]@row = "", [Column3]@row = "",[Column4]@row = ""), "Red", IF(AND(NOT(ISBLANK([Column1]@row)), NOT(ISBLANK([Column2]@row,, NOT(ISBLANK([Column3]@row,, NOT(ISBLANK([Column4]@row))), "Green"))

    · Share on FacebookShare on Twitter
  • Suzymae
    Suzymae ✭
    Options

    Hi Christina that worked when I removed the extra commas, but when I put something in the cells it gives me an error message

    · Share on FacebookShare on Twitter
  • Suzymae
    Suzymae ✭
    Options

    The error says#incorrect argument set

    · Share on FacebookShare on Twitter
  • Christina09
    Christina09 ✭✭✭✭✭✭
    Options

    @Suzymae

    Sorry, my mistake, I meant to put )), but put a ,

    =IF(AND([Column1]@row = "", [Column2]@row = "", [Column3]@row = "",[Column4]@row = ""), "Red", IF(AND(NOT(ISBLANK([Column1]@row)), NOT(ISBLANK([Column2]@row)), NOT(ISBLANK([Column3]@row)), NOT(ISBLANK([Column4]@row))), "Green"))

    Do you mind screenshot the error message and the formula?

    · Share on FacebookShare on Twitter
  • Suzymae
    Suzymae ✭
    Options

    =IF(AND(Safety@row = "", SCM@row = "", [ECS Director]@row = "", [Operations Director]@row = ""), "Red", IF(AND(NOT(ISBLANK(Safety@row)), NOT(ISBLANK(SCM@row, NOT(ISBLANK([ECS Director]@row, NOT(ISBLANK([Operations Director]@row))), "Green"))))))


    image.png


    · Share on FacebookShare on Twitter
  • Christina09
    Christina09 ✭✭✭✭✭✭
    Answer ✓
    Options

    @Suzymae

    Please see below screenshot for the formula

    image.png


    =IF(AND([Column1]@row = "", [Column2]@row = "", [Column3]@row = "",[Column4]@row = ""), "Red", IF(AND(NOT(ISBLANK([Column1]@row)), NOT(ISBLANK([Column2]@row)), NOT(ISBLANK([Column3]@row)), NOT(ISBLANK([Column4]@row))), "Green"))

    · Share on FacebookShare on Twitter
  • Suzymae
    Suzymae ✭
    Options

    That works, thank you so much!!!!!!!

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!