Formatting all Indented rows

Scott Rae

Hi,

Is there a way that I can change the colour of all cells in Indented rows rather than going thought them all one-by-one?

Cheers

Nate F

I found this thread by searching for conditional formatting by indent level, and a previous comment at the moment says try this:

=IF(COUNT(CHILDREN([Task Name]@row))  > 0), COUNT(ANCESTORS()) + 1)
That gives me an error, and appears to have wrong syntax for something, maybe in getting a count of ancestors? I don't understand it well enough yet to know why it errors. And I have no idea what the +1 is for.

My findings

This function will simply return the current row's indent level - ANCESTORS appears to be a list/array counting cells (values?) in the current column.
=COUNT(ANCESTORS())

But it will look at the column it's in, so instead, I'd rather point it to something that "is a row in use" if and only if that column is populated, such as a primary column, here called Task Name…
COUNT(ANCESTORS([Task Name]@row))

And if you only care to know the indent level if there are any children, this is what I'm using:
=IF(COUNT(CHILDREN([Task Name]@row)) > 0, COUNT(ANCESTORS([Task Name]@row)), "")

Hope this helps someone who was as confused as I was.

Andrée Starå

Hi Scott,

Yes!

I use this one all the time in my client solutions. I name the column Level and use that to simplify conditional formatting and more.

Try something like this.

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

The same version but with the below changes for your and others convenience.

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

Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

Did it work?

I hope that helps!

Have a fantastic weekend!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

 

Duane J. Hoffbauer

Andree,

I searched for formatting indented rows and this thread came up.

I hate to trouble you on this. I am lost on your solution you gave Scott. Is column level a unique column, or you talking the primary column? Then the formula you show... is that going in as a custom criteria? Maybe if you did a couple screenshots it would help.

Thank you kindly.

Paul Newcome

@Duane J. Hoffbauer You would create a separate column and insert the formula into every row making sure to change the column references within the formula to match a column name that you have in your sheet.

Andrée Starå

@Duane J. Hoffbauer

I'm always happy to help!

I saw that Paul answered already!

Let me know if I can help with anything else!

Best,

Andrée

Evan Darr

Hi,

I'm using this feature for formatting on indented rows and it worked beautifully when you paste the formula to existing cells. Is there a way to convert this to a column formula so that when new rows are added the formula autopopulates for that row? Currently, if a new row is added I would have to insert the formula and when I attempt to convert it to a column formula I get a syntax error because it breaks the rules of column formulas.

Thanks in advance for the help!

CJ Ponce

I tried (Expand all ) Select all the rows in any particular section, then change your background copy to the color you would like the child rows to be. Then (Collapse All) Select all rows in that section again, and then change to the color that you would like for the parent row to be. This worked for a sheet that already had over 300 rows.

Nate F

I found this thread by searching for conditional formatting by indent level, and a previous comment at the moment says try this:

=IF(COUNT(CHILDREN([Task Name]@row))  > 0), COUNT(ANCESTORS()) + 1)
That gives me an error, and appears to have wrong syntax for something, maybe in getting a count of ancestors? I don't understand it well enough yet to know why it errors. And I have no idea what the +1 is for.

My findings

This function will simply return the current row's indent level - ANCESTORS appears to be a list/array counting cells (values?) in the current column.
=COUNT(ANCESTORS())

But it will look at the column it's in, so instead, I'd rather point it to something that "is a row in use" if and only if that column is populated, such as a primary column, here called Task Name…
COUNT(ANCESTORS([Task Name]@row))

And if you only care to know the indent level if there are any children, this is what I'm using:
=IF(COUNT(CHILDREN([Task Name]@row)) > 0, COUNT(ANCESTORS([Task Name]@row)), "")

Hope this helps someone who was as confused as I was.

Paul Newcome

@Nate F The reason you were getting an error from the original formula is because you added an extra closing parenthesis after the "greater than zero" piece that isn't in the formula in the comment at the beginning of this thread.