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 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)))
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 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))
-
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)))
-
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
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.7K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!