Changes
Hierarchy functions have been renamed as follows:
All worksheets and templates that used the old hierarchy function names have automatically been upgraded to the new function names and should work as-is (i.e. no action is required on your part to adopt these changes).
This is great. I just realized that =COUNT(ANCESTORCELLS()) is a way of returning the indent level, where 0 would be no indent, 1 would be one indent level, etc. This means that WBS numbering can be automated by just changing the indent level.
By WBS numbering, I mean the outline style common to projects like this: 1, 1.1, 1.2, 1.3.1, 1.3.2, 1.4, 2, 2.1, etc.
Unfortunately, the WBS formula is crazy-complicated to make it robust to deleting and moving rows, and requires a helper column for the indent level.
Here is a very slick formula that is far more simple and also robust (because it is not referencing values from previous rows):
=IF(ISBLANK(PARENTCELL()),SIBLINGN(),PARENTCELL()&"."&SIBLINGN())
I’ve tested this by using a helper column in place of SIBLINGN(), and it works well.
All we need is a SIBLINGN() function that returns the position of the current cell among its siblings in the hierarchy. 1st Child would return 1, 2nd Child would return 2, etc. This would need to work for the level 0 cells also, where PARENTCELL() is blank. If you had no indenting at all, SIBLINGN() would just be the row number within the table.
I’ve shared a file called “Test WBS Numbering” so you can try it.
Here’s a proposal for indenting, to keep siblings aligned:
Good suggestions, we are considering both:
The hierarchical level of a cell is already easy:
It’s the position among siblings that is difficult to determine without a function.
Understood. Position among siblings is complicated by the fact that SSDC supports an arbitrary number of Views and each View may have its own sort and filter criteria. Also SSDC sorts the child rows of each distinct parent row separately.
We might consider a position function that returns the position of a row relative to its siblings in the “primary view” assuming no sorting and filtering is applied, but this may also lead to confusion. Open to thoughts and ideas here with the understanding that we aim to provide the simplest possible solution.
Currently, the only use I can think of for the sibling position is to enable a formula for automated WBS outline numbering that adjusts when rows are reordered and indented/outdented.
Here is a formula I was able to get to work in SSDC for automated WBS numbering in which the WBS number is in column C and column B is the indent level using the formula =COUNT(ANCESTORCELLS())
IF($B7="","-",IF($B7=1,COUNTIFS(OFFSET($B$6,0,0,ROW()-ROW($B$6),1),1)+1,IF($B7>OFFSET($B7,-1,0),OFFSET($C7,-1,0)&REPT(".1",$B7-OFFSET($B7,-1,0)),LEFT(OFFSET($C7,-1,0),FIND("^",SUBSTITUTE(OFFSET($C7,-1,0),".","^",$B7-1)))&(COUNTIFS(OFFSET($B$6,0,0,ROW()-ROW($B$6),1),$B7,OFFSET($C$6,0,0,ROW()-ROW($C$6),1),LEFT(OFFSET($C7,-1,0),FIND("^",SUBSTITUTE(OFFSET($C7,-1,0),".","^",$B7-1)))&"*")+1))))
Alternatively, a formula like the following would not require an extra indent level helper column, and is much more elegant. It doesn’t require the use of any OFFSET functions.
WBS =IF(ISBLANK(PARENTCELL()),SIBLINGN(),PARENTCELL()&"."&SIBLINGN())
With the features of indenting/outdenting, the hierarchical formulas, and defining task dependencies using related rows, there may be less need for WBS numbering. So, maybe you just wait and see how many people request outline numbering. In Google Sheets and Excel, WBS numbering allows you to create formulas that function like =MIN(DESCENDANTCELLS()), but perhaps the features and formulas in SSDC will make WBS numbering unnecessary.
Re: WBS numbering, we’ve had some internal discussions about this – concept stage, @Murali_Mohan has some interesting ideas. We’ll update this thread when we have something tangible to share.
