#Invalid Column Value

Cody_Gomez32

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)))

KPH

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

You would wrap the whole thing in an IFERROR.

=IFERROR(original_formula, "")

Cody_Gomez32

@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

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

@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.

SH Help 2.PNG

SH Help.PNG

James Keuning

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

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

Just need to adjust the parenthesis at the end.

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

Cody_Gomez32

@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

Great news @Cody_Gomez32 Glad we could help.