I want to update a cell if any of the cells in a certain range of cells equals a certain text.

Danielle Maroon

If ANY cells in Status3- Status7 = In Progress then I want Status2 to equal "In Progress, If ALL cells in Status3-Status7 = Complete then I want status2 to equal "Complete"

@Paul Newcome @Andrée Starå

Paul Newcome

@Eid E. Eid You are going to want to check your square brackets. The formula you provided would most likely throw an #UNPARSEABLE error.

@Danielle Maroon Andree's solution should work for you. My own personal take on it though would be:

=IF(CONTAINS("In Progress", CHILDREN()), "In Progress", IF(CONTAINS("Not Started", CHILDREN()), "Not Started", "Complete"))

Eid E. Eid

try something like this :

=IF(OR([Status3]="In Progress",[Status4]="In Progress",[Status5]="In Progress",[Status6]="In Progress",[Status7]="In Progress"),"In Progress",IF(AND([Status3]="Complete",[Status4]="Complete",[Status5]="Complete",[Status6]="Complete",[Status7]="Complete"),"Complete","")

Andrée Starå

@Danielle Maroon

Hi Danielle,

I'm guessing that you're using Parent/Children hierarchy.

Try something like this.

What do you want to show if one cell is complete? Should it be In Progress then maybe?

=IF(COUNTIF(CHILDREN(); "In Progress") > 0; "In Progress"; IF(COUNTIF(CHILDREN(); "") > 0; ""; IF(COUNT(CHILDREN()) = COUNTIF(CHILDREN(); "Complete"); "Complete")))

The same version but with the below changes for convenience.

=IF(COUNTIF(CHILDREN(), "In Progress") > 0, "In Progress", IF(COUNTIF(CHILDREN(), "") > 0, "", IF(COUNT(CHILDREN()) = COUNTIF(CHILDREN(), "Complete"), "Complete")))

Depending on your country/region, you'll need to exchange the comma to a period and the semi-colon to a comma.

Would that work?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

✅Did my post help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Paul Newcome

@Eid E. Eid You are going to want to check your square brackets. The formula you provided would most likely throw an #UNPARSEABLE error.

@Danielle Maroon Andree's solution should work for you. My own personal take on it though would be:

=IF(CONTAINS("In Progress", CHILDREN()), "In Progress", IF(CONTAINS("Not Started", CHILDREN()), "Not Started", "Complete"))

Andrée Starå

@Paul Newcome I always forget about CONTAINS.

I think someone is using this on me.

https://media.giphy.com/media/374pcIBVEGb6g/giphy.gif

Paul Newcome

@Andrée Starå Haha. I love the CONTAINS function.