Shorter way to do this?

Sarah Harrison

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

Jeff Reisman

@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.

Sarah Harrison

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

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