INVALID COLUMN VALUE
Can't figure out why I'm getting this when none of the Children for Deliverables and Tasks are checked off in the checkbox column. Works fine as long as one of them is checked.
=IFERROR(INDEX(CHILDREN([Deliverables and Tasks]@row), COUNTIFS(CHILDREN([Ready to Hand-Off to Next Task Owner]@row), 1)), "")
Thanks!
Best Answer
-
Hi @Jeana
It looks like the INDEX is having a hard time when the COUNTIFS returns 0. We can add an IF statement at the front to say that if no boxes are checked, return a blank cell... otherwise, perform your INDEX statment.
Try this:
=IF(COUNTIFS(CHILDREN([Ready to Hand-Off to Next Task Owner]@row), 1) = 0, "", INDEX(CHILDREN([Deliverables and Tasks]@row), COUNTIFS(CHILDREN([Ready to Hand-Off to Next Task Owner]@row), 1)))
Cheers!
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
Answers
-
I think it's because I"m referencing a checkbox column. Do I just need a helper column to translate the checkbox to a "yes" or "no" and reference that in the formula? This works but is there a better way?
Jeana
-
Hi @Jeana
I think the problem comes from how the INDEX formula works.
If I understand how you set it up, if no checkbox is marked, then your COUNTIF returns 0 as the lin in your CHILDREN range which starts at 1 I believe.
Maybe you could try to rewrite your formula using:
=INDEX(COLLECT(CHILDREN([Deliverables and Tasks]@row), CHILDREN([Ready to Hand-Off to Next Task Owner]@row), 1),1)
Should do it.
-
Thanks David but now I'm getting #INVALID VALUE error if I don't have any of the boxes checked for the CHILDREN. Hhhhmmmmm....
Jeana
-
What I am wanting is to check the CHILDREN in the column "Deliverables and Tasks" and if the CHILD row has a check mark for "Ready to Hand-off to Next Task Owner " then display the value for Deliverables and Tasks for the row where the box is checked. This is returning the value correctly when a box is checked using this formula:
=IFERROR(INDEX(CHILDREN([Deliverables and Tasks]@row), COUNTIFS(CHILDREN([Ready to Hand-Off to Next Task Owner]@row), 1)), "")
The issue is that when NO boxes are checked it shows the error message. I want it to be blank if no boxes or checked.
Any ideas would be greatly appreciated.
Jeana
-
Hi @Jeana
It looks like the INDEX is having a hard time when the COUNTIFS returns 0. We can add an IF statement at the front to say that if no boxes are checked, return a blank cell... otherwise, perform your INDEX statment.
Try this:
=IF(COUNTIFS(CHILDREN([Ready to Hand-Off to Next Task Owner]@row), 1) = 0, "", INDEX(CHILDREN([Deliverables and Tasks]@row), COUNTIFS(CHILDREN([Ready to Hand-Off to Next Task Owner]@row), 1)))
Cheers!
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
Perfect! Thanks so much Genevieve!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.3K Get Help
- 364 Global Discussions
- 199 Industry Talk
- 428 Announcements
- 4.4K Ideas & Feature Requests
- 136 Brandfolder
- 127 Just for fun
- 128 Community Job Board
- 444 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 283 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!