#Invalid Column Value

Options

I have this formula that is working correctly. I just want to add an IFERROR or IF(ISBLANK) to the formula so when the child row is blank, the parent row that this formula is in, will be blank until the child row has data entered. Below is the formula I am currently using.

=INDEX(CHILDREN([Movment History]@row), COUNT(CHILDREN([Movment History]@row)))

Best Answer

  • KPH
    KPH ✭✭✭✭✭✭
    Answer ✓
    Options

    I wonder if we have misunderstood the question.

    Do you need a formula to identify if a row has children?

    If so this will do the job:


    Here it is to copy/paste

    =IF(COUNT(CHILDREN([Movment History]@row)) > 0, "parent", "not parent")

    You can combine that with your formula

    =(INDEX(CHILDREN([Movment History]@row), COUNT(CHILDREN([Movment History]@row))))

    Like this (your formula in bold):

    =IF(COUNT(CHILDREN([Movment History]@row)) > 0, (INDEX(CHILDREN([Movment History]@row), COUNT(CHILDREN([Movment History]@row)))), "not parent")

    Now, if the row is a parent it will execute your formula, and if it is not, it will put in "not parent". You can change "not parent" to "".

    If you need something other than identifying if the row is a parent you can adjust the first part of the IF (shown in bold here) to the logic that suits.

    =IF(COUNT(CHILDREN([Movment History]@row)) > 0, (INDEX(CHILDREN([Movment History]@row), COUNT(CHILDREN([Movment History]@row)))), "")

    Hope this helps.

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    You would wrap the whole thing in an IFERROR.

    =IFERROR(original_formula, "")

  • Cody_Gomez32
    Cody_Gomez32 ✭✭✭
    edited 11/02/23
    Options

    @Paul Newcome I tried that. I am getting #INCORRECT ARGUMENT SET. Is it how I am tying the two formulas together?

    =IFERROR(INDEX(CHILDREN([Movment History]@row), COUNT(CHILDREN([Movment History]@row, " "))))

  • KPH
    KPH ✭✭✭✭✭✭
    Options

    In your version you have the ,"") that should be added at the very end, added to your original formula.

    You have

    =IFERROR(INDEX(CHILDREN([Movment History]@row), COUNT(CHILDREN([Movment History]@row, " "))))

    It should be

    =IFERROR(INDEX(CHILDREN([Movment History]@row), COUNT(CHILDREN([Movment History]@row))), "")

  • Cody_Gomez32
    Options

    @KPH I tried your formula and I am still getting the #INVALID COLUMN VALUE.

    =IFERROR(INDEX(CHILDREN([Movment History]@row), COUNT(CHILDREN([Movment History]@row))), "")

    I have attached snip its of my sheet that I am working on.


  • James Keuning
    James Keuning ✭✭✭✭✭
    Options

    What do you want this cell to do when the child has data in it? I can't tell which range you are indexing, or why.

  • KPH
    KPH ✭✭✭✭✭✭
    Answer ✓
    Options

    I wonder if we have misunderstood the question.

    Do you need a formula to identify if a row has children?

    If so this will do the job:


    Here it is to copy/paste

    =IF(COUNT(CHILDREN([Movment History]@row)) > 0, "parent", "not parent")

    You can combine that with your formula

    =(INDEX(CHILDREN([Movment History]@row), COUNT(CHILDREN([Movment History]@row))))

    Like this (your formula in bold):

    =IF(COUNT(CHILDREN([Movment History]@row)) > 0, (INDEX(CHILDREN([Movment History]@row), COUNT(CHILDREN([Movment History]@row)))), "not parent")

    Now, if the row is a parent it will execute your formula, and if it is not, it will put in "not parent". You can change "not parent" to "".

    If you need something other than identifying if the row is a parent you can adjust the first part of the IF (shown in bold here) to the logic that suits.

    =IF(COUNT(CHILDREN([Movment History]@row)) > 0, (INDEX(CHILDREN([Movment History]@row), COUNT(CHILDREN([Movment History]@row)))), "")

    Hope this helps.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    Just need to adjust the parenthesis at the end.


    =IFERROR(INDEX(CHILDREN([Movment History]@row), COUNT(CHILDREN([Movment History]@row))), " ")

  • Cody_Gomez32
    Options

    @Paul Newcome @KPH Thank you both for the help. @KPH Your formula did the trick. =IF(COUNT(CHILDREN([Movment History]@row)) > 0, (INDEX(CHILDREN([Movment History]@row), COUNT(CHILDREN([Movment History]@row)))), "not parent").

    I changed the "not parent" to just be " ". This will make the parent cell blank when the most recent child row has no data. Once there is data in that lowest child cell, it will update the parent row to show the most recent child row data.

  • KPH
    KPH ✭✭✭✭✭✭
    Options

    Great news @Cody_Gomez32 Glad we could help.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!