INVALID COLUMN VALUE

Jeana

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!

Genevieve P.

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

Jeana

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

David Joyeuse

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.

Jeana

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

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

Genevieve P.

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

Jeana

@Genevieve P

Perfect! Thanks so much Genevieve!