CHILDREN() and INDEX() Killing Me!

dotson98

Hello-

I need to pull the value from the 1st child row, 3rd column.  If that value is == to "Hold", then the cell should return a value of "Hold".

I'm trying to follow the help guide as well as some community posts, but I'm not getting it.  What is the proper syntax?

The goal is to have the parent row's "Current Status" cell show a green check if the "Action Type" cell of the 1st child row is set to "Release".  Using if statements, then if the "Action Type" cell is "Hold", then the "Current Status" cell will also be set to "Hold".  And if "Action Type" is "Purge", then "Current Status" will show "Hold".  That's just so that I can get the Green/Yellow/Red colors.

Paul Newcome

The problem is that you are using CHILDREN().

 

When using this function, if you don't specify a cell then it automatically assumes that you are referencing the children of the current cell. Because it is referencing the children of the cell that the formula is in, it is only referencing 1 column, so using 3 as your column number in the INDEX formula won't work. You don't have 3 columns for your INDEX to look at.

 

Try specifying the cell in the CHILDREN function and ignore the column number portion of the INDEX function (it defaults to 1).

 

=IF(INDEX(CHILDREN([Action Type]@row), 1) = "On Hold", "Yes", "No")

dotson98

OK - I believe I understand.  I didn't realize that you could reference the child cell.

So I've got things working pretty well.  Can you have a formula that references children rows of a different parent?  Here is an example.  In the screenshot, row 1 represents the top entity in the company.  Row 4, 7, 9 etc, are all subsidiaries of the top entity.  Each subsidiary can be put on "hold" without it affecting the other subsidiaries.  However, if the top entity is put on hold, then all subsidiaries are put on hold as well.

Here is my original formula for the subsidiary entities.  It checks to see if its first child row is on hold, OR if the top entity is on hold.

=IF(OR(INDEX(CHILDREN([Action Type]@row), 1) = "On Hold", $[Action Type]$2 = "On Hold"), "No", IF(INDEX(CHILDREN([Action Type]@row), 1) = "Purging", "Hold", IF(ISBLANK(INDEX(CHILDREN([Action Type]@row), 1)), "", "Yes")))

I have bolded the formula that checks to see if the top entity is on hold.  This works fine, until during testing I added new rows to the top entity and then deleted them.  $[action type]$2 is being incremented.  When I delete that row, you see the error in the screenshot.  

Therein lies my question.  Can the formulas for the subsidiaries (rows 4, 7, 9) reference the children of row 1?

dotson98

Looks like my comments with images have to be approved.  I've posted the same thing twice and it's held up...