Levels formula

David Clunie

Hi all

I am new to smart sheets, and I was after the formula for Levels and cross-referencing % to give a result.

I have the following code in levels, but I need this to be more intelligent.

=COUNT(ANCESTORS())

This returns the Uppermost parent as 0, Next level as 1, last level as 2.

Is there a way of looking at the % column to evaluate if <100% then if so

Return the Uppermost parent as 0.1, Next level as 1.1, last level as 2.1

If = to 100% then leave as is?

This will allow me to report the metrics to show complete and incomplete projects to team members.

Also, is there a way to add to the full column, so I don't need to copy the formula down when I add new rows and projects?

Many Thanks

David Clunie

Hi , Thanks for your reply but I don’t think this is the correct format that I need.

Is there a way to do do the following Numbering in projects.

1 as Ancestor

2 as Parent

2.0 as first child

2.1 as Second child

........etc

3 as second parent if found

3.0 as first child

3.1 as Second child

........etc

1 as new Ancestor if new group started

thanks

Andrée Starå

Hi @David Clunie

I usually add another version of your formula.

Something like this.

=IF(COUNT(CHILDREN([Task Name]@row)) > 0, COUNT(ANCESTORS()) + 1)

You can modify it, and as long as you're not referencing a specific row number, you can convert it to a Column Formula that would autofill for new rows.

Make sense?

Would that work/help?

I hope that helps!

Have a fantastic week & Happy Holidays!

Best,

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

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

David Clunie

Hi , Thanks for your reply but I don’t think this is the correct format that I need.

Is there a way to do do the following Numbering in projects.

1 as Ancestor

2 as Parent

2.0 as first child

2.1 as Second child

........etc

3 as second parent if found

3.0 as first child

3.1 as Second child

........etc

1 as new Ancestor if new group started

thanks