INVALID COLUMN VALUE

Options
Jeana
Jeana ✭✭✭✭✭✭
edited 12/06/20 in Formulas and Functions

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

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓
    Options

    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

Answers

  • Jeana
    Jeana ✭✭✭✭✭✭
    Options

    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
    David Joyeuse ✭✭✭✭✭
    Options

    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
    Jeana ✭✭✭✭✭✭
    Options

    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
    Jeana ✭✭✭✭✭✭
    Options

    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.
    Genevieve P. Employee Admin
    Answer ✓
    Options

    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
    Jeana ✭✭✭✭✭✭
    Options

    @Genevieve P

    Perfect! Thanks so much Genevieve!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!