Reference the Children of the Parent of a Cell as the Range

Jacob Slaathaug

I'm looking for a way to auto number my WBS.  I believe I have found a way using logic functions combined with the Match function. The issue I am running into is that I am unable to reference a cell, say Task5 and then specify its "siblings" as the range. I have tried to specify the range as Children(Parent(Task5)), but I receive an error each time I do.  The formula works if I specify the parent cell. For instance Children(Task3), but I would have to edit this for each parent.  I'm look for something that I can fill in the entire column.

This would be my desired formula (for row 5), but the range portion (in bold) is not valid.

=VALUE(MATCH(Task5, CHILDREN(PARENT(Task5)), 0))

In the photo the formula is in the Code column and produces the numbering shown.

Thanks in advance.

Shaine Greenwood

Hi Jacob—

There currently isn't a way to specify a range but you might consider using the @row parameter in the cell reference of a PARENT or CHILDREN function.

Your function would look more like this:

=VALUE(MATCH(Task@row, PARENT(Task@row), 0))

You may also need to do the same with CHILDREN depending on your hierarchy:

=VALUE(MATCH(Task@row, CHILDREN(Task@row),0))

@row is designed to reference the cell on the same row as the formula. More on this parameter here: https://help.smartsheet.com/articles/2476491-create-efficient-formulas-with-at-cell