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

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 AccountableReport: 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([AccountableReport: Fill Down]@row)))
Answers

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))

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 AccountableReport: 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))

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 AccountableReport: 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([AccountableReport: Fill Down]@row)))

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

Glad to have helped.
Help Article Resources
Categories
Check out the Formula Handbook template!