Any ideas why a formula wouldn't work on all rows?

Robin H 77

I have a formula that is working on at least one row, but not all rows, and I have the necessary data in all rows for it to work, so I am boggled as to why it isn't working on all rows

Screenshot 2024-05-13 at 7.55.46 AM.png

:

=IF([#Children]@row > 0, IF(COUNTIF(CHILDREN(), "MISSING DATES") > 0, "MISSING DATES", IF(COUNTIF(CHILDREN(), "Red") > 0, "Red", IF(COUNTIF(CHILDREN(), "Yellow") > 0, "Yellow", IF(COUNTIF(CHILDREN(), "Green") > 0, "Green", "Blue")))), IF(ISBLANK([Task Name]@row), "", IF([# Workdays]@row = "MISSING DATES", "MISSING DATES", IF(OR(Status@row = "Complete", Status@row = "Cancelled", Status@row = "Deferred", Status@row = "On Hold"), "Blue", IF(OR([Days Til Due]@row = "Past Due", Status@row = "In Jeopardy"), "Red", IF(OR(AND(Status@row = "Not Started", Start@row < TODAY()), Status@row = "Challenged"), "Yellow", "Green"))))))

KPH

As it is a nested IF formula, it is probably working for rows that evaluate to true in one of the earlier IFs, then failing when it reaches the part of the formula that has a problem in it. I would change all the outcomes to unique words and test it. See which words appear and which do not. The problem will be after the part of the formula that returns the last word. You can then focus on troubleshooting that part.

Something like this:

=IF([#Children]@row > 0, IF(COUNTIF(CHILDREN(), "MISSING DATES") > 0, "MISSING DATES", IF(COUNTIF(CHILDREN(), "Red") > 0, "cow", IF(COUNTIF(CHILDREN(), "Yellow") > 0, "pig", IF(COUNTIF(CHILDREN(), "Green") > 0, "Emu", "Goat")))), IF(ISBLANK([Task Name]@row), "", IF([# Workdays]@row = "MISSING DATES", "MISSING DATES", IF(OR(Status@row = "Complete", Status@row = "Cancelled", Status@row = "Deferred", Status@row = "On Hold"), "Rabbit", IF(OR([Days Til Due]@row = "Past Due", Status@row = "In Jeopardy"), "Chick", IF(OR(AND(Status@row = "Not Started", Start@row < TODAY()), Status@row = "Challenged"), "Hedgehog", "Mink"))))))

KPH

As it is a nested IF formula, it is probably working for rows that evaluate to true in one of the earlier IFs, then failing when it reaches the part of the formula that has a problem in it. I would change all the outcomes to unique words and test it. See which words appear and which do not. The problem will be after the part of the formula that returns the last word. You can then focus on troubleshooting that part.

Something like this:

=IF([#Children]@row > 0, IF(COUNTIF(CHILDREN(), "MISSING DATES") > 0, "MISSING DATES", IF(COUNTIF(CHILDREN(), "Red") > 0, "cow", IF(COUNTIF(CHILDREN(), "Yellow") > 0, "pig", IF(COUNTIF(CHILDREN(), "Green") > 0, "Emu", "Goat")))), IF(ISBLANK([Task Name]@row), "", IF([# Workdays]@row = "MISSING DATES", "MISSING DATES", IF(OR(Status@row = "Complete", Status@row = "Cancelled", Status@row = "Deferred", Status@row = "On Hold"), "Rabbit", IF(OR([Days Til Due]@row = "Past Due", Status@row = "In Jeopardy"), "Chick", IF(OR(AND(Status@row = "Not Started", Start@row < TODAY()), Status@row = "Challenged"), "Hedgehog", "Mink"))))))

Robin H 77

That's a great idea, thanks!

Robin H 77

Your suggestion helped me find the problem and fix it, thanks again!

KPH

Wonderful! Glad I could point you in the right direction.