Shorter way to do this?

Options

I am using a formula to change the value of one cells based on the values of some cells below it in a column. The formula that I'm using is working but I want to add it to some more places in the sheet, where it would get very long. Does anyone know a way I could abbreviate this?

It is okay when there are only two cells that I am basing the value off.

E.g. =IF(AND(OR(Status6 = "Complete", Status6 = "N/A"), OR(Status7 = "Complete", Status7 = "N/A")), "Complete")

But it gets much longer when there are 6+ cells.

E.g. =IF(AND(OR(Status9 = "Complete", Status9 = "N/A"), OR(Status10 = "Complete", Status10 = "N/A"), OR(Status11 = "Complete", Status11 = "N/A"), OR(Status12 = "Complete", Status12 = "N/A"), OR(Status13 = "Complete", Status13 = "N/A"), OR(Status14 = "Complete", Status14 = "N/A"), OR(Status15 = "Complete", Status15 = "N/A")), "Complete")

Tags:

Answers

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Options

    @Sarah Harrison

    Could you share a screenshot or mock up of your sheet? That may shed some light on what you're trying to accomplish and help us find a more efficient way to do what you need.

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • Sarah Harrison
    Options

    Status5 and Status8 have the two formulas that I shared above. When Status6:Status7 are marked as either Complete or N/A, then Status5 turns to Complete. When Status9:Status15 are marked as either Complete or N/A, then Status8 turns to Complete.

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    edited 02/08/22
    Options

    Ok, I see what you're doing. You have parent/child rows, and want to show complete on the parent row when the child rows are Complete or N/A.

    Are there other values the status could be? Or are those the only two choices besides blank?

    I'm thinking of a formula for your PARENT status fields that counts the number of child rows under each parent row, and also counts child status cells with either "Complete" or "N/A", and if the two counts match, then it sets the parent status field to "Complete". Let's see:

    =IF(COUNT(CHILDREN(Task@row)) = COUNTIF(CHILDREN(Status@row), OR(@cell = "Complete", @cell = "N/A")), "Complete", "")

    (This assumes your Task column is called "Task"; I can't see the whole thing in your screenshot. It also assumes each child row has a value of some sort in Task.)

    This will work on all Parent Status fields, since you're not referencing specific row numbers. When you enter the formula, make sure your parentheses match (same color for the first parentheses after "IF" and for the last parentheses in the formula.)

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!