I need the value in the Accountable column at a Level 3 added to it's children. Any ideas? Thanks!

Terri1019

KPH

Hi @Terri1019

There are two problems with the formula:

1) If you are not changing the Accountable column then for L4 and beyond then PARENT(Accountable@row) is blank and therefore blank is returned.

=IF(COUNT(ANCESTORS(Accountable@row)) >= 3, PARENT(Accountable@row))

You could remove this issue by using nested IFs, one for =3 that pulls the value from Accountable and one for greater than 3, that pulls the value from Accountable-Report: Fill Down.

However, that brings us to issue 2:

2) If you count ancestors on the Accountable column and this is blank the number of ancestors will not increase. Column 5 here just has a formula to count the ancestors. See how it stops at 3?

So you need to base the count of ancestors on a column that is always populated. Here is what is looks like using the Level column.

=IF(COUNT(ANCESTORS(Level@row)) = 3, PARENT(Accountable@row), IF(COUNT(ANCESTORS(Level@row)) > 3, PARENT([Accountable-Report: Fill Down]@row)))

KPH

This would copy the parent value for accountability to any children

=IF(COUNT(ANCESTORS()) > 0, PARENT(Accountable@row))

So L4 would have the L3 value, L5 would have the L4 value (which would be the L3 value).

This would copy the parent value for accountability to children at L4 only. These are children with 3 ancestors.

=IF(COUNT(ANCESTORS()) = 3, PARENT(Accountable@row))

Terri1019

Thank you for your help! I apologize for my slow response - I have been out of the office. I see now I wasn't clear in my question. I don't want the fields altered in the Accountable column (the entry will happen here) but I would like it populated down to all levels in the Accountable-Report: Fill Down column. I tried this formula to carry the values down from Level 3 to all levels below but it is only filling in the Level 4 field with the Level 3 value and not carrying that value to all levels under the L3. Any thoughts you have are much appreciated!!!

=IF(COUNT(ANCESTORS(Accountable@row)) >= 3, PARENT(Accountable@row))

KPH

Hi @Terri1019

There are two problems with the formula:

1) If you are not changing the Accountable column then for L4 and beyond then PARENT(Accountable@row) is blank and therefore blank is returned.

=IF(COUNT(ANCESTORS(Accountable@row)) >= 3, PARENT(Accountable@row))

You could remove this issue by using nested IFs, one for =3 that pulls the value from Accountable and one for greater than 3, that pulls the value from Accountable-Report: Fill Down.

However, that brings us to issue 2:

2) If you count ancestors on the Accountable column and this is blank the number of ancestors will not increase. Column 5 here just has a formula to count the ancestors. See how it stops at 3?

So you need to base the count of ancestors on a column that is always populated. Here is what is looks like using the Level column.

=IF(COUNT(ANCESTORS(Level@row)) = 3, PARENT(Accountable@row), IF(COUNT(ANCESTORS(Level@row)) > 3, PARENT([Accountable-Report: Fill Down]@row)))

Terri1019

That is perfect. Thank you so much for your help with this!! I have alot to learn!!!

KPH

Glad to have helped.