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

Best Answer

  • KPH
    KPH ✭✭✭✭✭✭
    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

  • KPH
    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
    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
    KPH ✭✭✭✭✭✭
    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)))



  • Terri1019
    Terri1019 ✭✭✭

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

  • KPH
    KPH ✭✭✭✭✭✭

    Glad to have helped.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!