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.