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