What is a formula to look at series of columns, and check the box for the rightmost checked column

Corey McCarren
Corey McCarren ✭✭✭
edited 01/10/24 in Formulas and Functions

For example, I have a series of columns as follows, that go from left to right from beginning of process to the end of the process:

End of Feature Development

End of New Sale

End of Renewal

End of Support

End of Life

They are all checkbox columns. I would like a formula to look at all of the columns, and fill in the field with the rightmost column name. ie, if a row is checked up to "End of Renewal," I would like the field to fill with the text End of Renewal. If no columns are checked, I would like it to default to "Active"

Best Answer

  • AravindGP
    AravindGP ✭✭✭✭✭
    Answer ✓

    Hi @Corey McCarren


    You will need to write a nested IF formula, starting with the last part of the process. An example is something like this =IF([End of Life]@row =1, "End of life", IF([End of Support]@row = 1, "End of Support", If([End of Renewal]@row =1, "End of Renewal", IF([End of New Sale]@row =1, "End of New Sale", etc. and the last false statement will be "Active"

    Thanks,

    Aravind

    Reach out for any help on licenses, configuration, or training

Answers

  • AravindGP
    AravindGP ✭✭✭✭✭
    Answer ✓

    Hi @Corey McCarren


    You will need to write a nested IF formula, starting with the last part of the process. An example is something like this =IF([End of Life]@row =1, "End of life", IF([End of Support]@row = 1, "End of Support", If([End of Renewal]@row =1, "End of Renewal", IF([End of New Sale]@row =1, "End of New Sale", etc. and the last false statement will be "Active"

    Thanks,

    Aravind

    Reach out for any help on licenses, configuration, or training

  • That worked, thank you!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!