auto-numbering

Hollina Ung

Can you use the auto-number functionality to number hierarchically? i.e.

1.

1.1

1.2,

1.21

1.22

1.3

2.

 

If so, how do you set it up? Thank you

Brett Evans

I built a system that automagically does the heirarchical numbering pictured below.  It is now one of my main templates.  It has a lot of nested IF STATEMENTS.    Is that what you are looking for?

Hollina Ung

That is exactly what I'm looking for. 

 

If if you be willing to share your formula/s, it would be greatly appreciated. Thank you

 

Well done. 

Brett Evans

Please contact me at brettATalumniDOTbrownDOTedu.  There is a bunch of hidden functionality to walk thru, I can't type it up quickly here.

John Sauber

Hi Brett, 

 

It would be helpful (if you still had this sheet) to publish it so users could see how it works (if the formulas were visible or commented).

 

I have been able to get close to this, but I fall short if an object is not the top- or the bottom-level hierarchy AND doesn't have any children.

 

If you still have anything on this, that would be great!

John Sauber

The sheet linked here shows how far we've been able to get. We cannot show what level an item is at without having any visibility from child to parent, like we have from parent to child. All we're really able to do here is to show how deep someone's "parent" status goes. For instance, if you're a great-grandparent, then you're by definition a grandparent and a parent as well.

 

If anyone can extend this to extract what generation someone is within this list formulaicly, then we're really on to something.

 

The Genration column is not calculated, but I put what the calculation should come out to. The problem is that Peter should be generation 2, but his non-parent status makes it difficult (impossible?) to imply. I have tried various permuatations counting the size of subfamilies with the COUNT(), SUMIFS(), COUNTIFS() formulas, but I can't seem to make it to the finish line here.

 

You can see some conditional formatting coming into play here, and if we could calculate the Generation column automatically, then this conditional formatting would build itself right into the hierarchical sheet.

 

https://app.smartsheet.com/b/publish?EQBCT=b6f3864838f2441eb7ce7260fa4ae022

Steve N

Hi Brett and John

I'd like to offer my team auto numbering of a hierarchy.  Can you save me time in not having to re-invent the wheel and share me your sheets?

John: your sheet is no longer shared.

 

Brett: shall I contact you via LinkedIn?

Thanks,

Steve