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

Jacob Slaathaug
edited 12/09/19 in Formulas and Functions

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.

Capture.PNG

Tags:

Comments

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!