Nested Parent Child Column Formula

I have a field that indicates it is the parent row. Header is the column name. It is a check box. So I am trying to get this formula working is giving me an error.

=if(heading@row=1,[Group Attendance]@row + 1,if(complete@row=0,Parent(@column)))

I want to show the total credits for attendance in the parent row field, and carry this value from the parent field in each of the fields below. Can I reference the parent row in this column formula?

There is a field in my row that indicates the attendance is already entered and I dont want the total attendance credits to show up in this column. So I have the If(Complete@row=0 for the children formula. I believe the problem is referencing the parent row in the column formula? Says #BLOCKED

Answers

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    edited 02/08/23

    #BLOCKED usually indicates that there is an error in one of the cells being referenced in the formula. However, in this case I think there's more to it than that.

    First, there is no "@column" function in Smartsheet. You can reference the parent cell of a cell on the row you're in by using PARENT([Column Name]@row).

    Next, You say the checkbox column is called Header, so let's fix that, plus change functions to all caps, add some spaces, and enclose calculations in parentheses.

    =IF(Header@row = 1, ([Group Attendance]@row + 1), IF(Complete@row = 0, PARENT([Column Name]@row))

    In looking at the above, make sure your logic is correct. The logic says if the Header checkbox on this row is checked, set this cell to equal the Group Attendance value on this row plus 1. If the Header checkbox is not checked, go to the next IF, which looks for the Complete checkbox on this row to be unchecked, and if it is, to set this cell to equal the value of some column on the Parent row of this row. Does that sound right? What should it do if the Header checkbox is unchecked, and the Complete checkbox IS checked?

    A screenshot of your sheet (with any sensitive information covered) would help a lot too.

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • Thank you. I am missing something. UNPARSED. appreciate your glance to see the paren or comma that I missing.

    =IF([header]@row=1,([Group Attendance]@row + 1), IF([Complete]@row=0, PARENT([group attendance]@row)))

  • To answer your question about what should be in the cell if header is unchecked.

    if complete is checked = cell should be blank,

    if complete is unchecked = The children should carry down the value of the column parent.

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Cheryl Hishmeh

    Would you be able to post a screen capture of the formula open in your sheet, and the column names showing? But please block out sensitive data.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!