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

Find more posts tagged with

Accepted answers

All comments

Jeff Reisman

https://community.smartsheet.com/discussion/100876/nested-parent-child-column-formula

#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.

Cheryl Hishmeh

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

Cheryl Hishmeh

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.

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.

wallacea

Is there a video of this function? I'm afraid I'm not able to follow the thread - I would do better with seeing it performed.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.