Automating hierarchy indenting using formulas

Monica

Hello All,

Is there a formula to automate hierarchy?  I am trying to use Smartsheet to create a family tree and dashboard.  I have created a column labeled "Generation Tier" and I am using the people/person symbol to represent where individuals fall in the generations (child=1 person, grandchild=2 people, great grand=3 people, etc.)

It's a very large family so I would like to automate the hierarchy by either:

- automatically indenting the row based on the Generation Tier selected.

or

- automatically change the Generation Tier depending on the number of times the row has been indented.

All feedback will be greatly appreciated.

Monica

 

 

 

Paul Newcome

The only way would be to write an IF statement that will automate the Generation Tier based on the indentation. This would require a helper column. In the helper column you would enter the formula

=ANCESTORS([Primary Column]@row)

Then drag-fill on down. (Should auto-populate when new rows are added afterwards) This will generate a number based on how many parent rows are above it.

You would then write a nested IF statement in the Generation Tier column along the lines of

=IF([Helper Column]@row = 0, Empty, IF([Helper Column]@row = 1, One, IF([Helper Column]@row.............

Of course you would need to change the column names to whatever you happen to be using.

If you don't want the helper column cluttering things up, you could do what I do (overkill honestly). Stick it all the way on the far right of the sheet. Change the fill and font color to white. Lock the column. Change the width down to as thin as it will let you. Then hide the column. Keeps things looking nice and clean, but allows you to use as many helper columns as you need. 

Monica

Thanks a bunch!  I was able to use your formula examples to automate the Generation Tier column.  That made it easier and user error proof.

Thanks again.

Monica

Paul Newcome

Glad to be of assistance. 

Monica Solar

Hi @Paul Newcome - my name is also Monica, but not the same as above :).

I'm trying to do something similar. I have a column named "Project Plan / WBS Hierarchy" and based on the type of task in that column, I want to indent my "Activity Name" column.

For example, "Workstream" items should not be indented at all, Subfunction should have 1 indent space, etc

Workstream 0 (no indenting)

Subfunction 1 indent space

Milestone 2 indent spaces

Task 3 ident spaces

Sub Task 4 indent spaces

Any thoughts on how to do this?

thank you!

Monica

Andrée Starå

Hi @Monica Solar

I hope you're well and safe!

Unfortunately, it's not possible now to automatically indent/create a hierarchy, but it's an excellent idea!

Please submit this as a Product Feedback or Idea (If it hasn't been added already) when you have a moment.

Here's a possible workaround or workarounds

• Smartsheet API
• 3rd-party service

Would any of those options work/help?

I hope that helps!

Be safe, and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Monica Solar

Hi @Andrée Starå thanks so much! I'll submit to Product Feedback. Not really looking for API or 3rd party, was hoping I could do something with a formula.... Have a great day! Thanks!

Andrée Starå

@Monica Solar

Excellent!

Happy to help!

✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up/Awesome or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!