Automating the Formatting of Cell Hierarchy

Dereck H

Is there a way to automate the formating of your parent and child rows? If I have 4 layers of heiarchy (h1 > h2 > h3 > h4), is there a way smartsheet can automatically formate each layer of heiarchy? For instance, telling smartsheet to make the top parent rows (h1) BOLD at 16pt font while making all h4 child rows italicised at 8pt font. I find manually having to shift select each row and adjusts its parameters group by group is very inefficient and redudant, especially when you want to add new rows that don't start out with the same size/font/color formating you want the heiarchy to have, this is something that should be programed with presets.

Kennedy Stomps

Hi Dereck-- Great question! There isn't the ability to automate this formatting (without using a workaround) at this point, but I've added your vote for this to our enhancement request list.

 

In the meantime, you might consider using our new ANCESTORS() function to determine the hierarchy level of each row, then use Conditional Formatting rules to apply formatting based on the results of the function. You'll want to create a new column in your sheet, then add a ANCESTORS() function referencing your Primary column:

 

=COUNT(ANCESTORS([Primary Column]1))

 

This will count the number of parent row ancestors of a row, which will tell you how deeply nested in a hierarchy a row is. If the result is "blank", the row is the highest parent row. A value of "1" would mean the row has one parent, "2", a parent and a grandparent, and so on. You could then set up Conditional Formatting rules for each number needed. Hope this helps!

Dereck H

Ahhh, this is a much better workaround than manually shift selecting rows, thanks for the help! 

Tommy James

Another idea would be the ability to identify a row as a leaf (member with no children).  I often want to treat the leaf members differently than the parents.

Dereck H

I agree, great idea, make sure to put that vote in

Kennedy Stomps

Hi Tommy and Dereck-- I've added your votes for this! 

J. Craig Williams

Tommy,

 

Is a "leaf" a member with no children AND no parent? Or just a member without children?

 

Craig

Tommy James

Yes....a leaf members is at the "bottom" of the "tree" and has nothing below it.  Would not matter if it has any parents or not.

 

I often want to be able to apply formatting to or do calcs for members with no children separately from members that do have children.

 

Thanks

SarahW

+1 for this. We are using the count(ancestors()) with conditional formatting rules approach as a workaround, but people often forget to drag down the formula and it does not auto-fill when people paste in or add multiple rows at a time.  We then have to go in and drag the formula down or manually format.  The conditional formatting rules do not always seem to function.

LMachado

I'd like to know the status of this! I am using this work around, and a form for intake with a hidden field and it's value automatically set at 1... but it's breaks the "autofill" feature since it's inserted as a number and not a formula.

LMachado

Please add my vote!

sowjanya.manukonda74776

Hi,

i have a smartsheet with data in different groups. I am trying to format all the data with a specific formula but unable to do so. I am able to format cells under a group only. I need to format cells in different groups too. Please help.

 

Attached screenshot where i am unable to format the same formula to below rows

Andrée Starå

Hi,

If you double-click on the painter icon, it will lock the format, and you can use it in multiple cells more efficiently.

Hope that helps!

Have a fantastic day!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Mike in IT

Add my vote to this idea. Also, how would I add my vote on my own?