Any ideas why a formula wouldn't work on all rows?
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
:
=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"))))))
Best Answer
-
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"))))))
Answers
-
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"))))))
-
That's a great idea, thanks!
-
Your suggestion helped me find the problem and fix it, thanks again!
-
Wonderful! Glad I could point you in the right direction.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!