Numbering Parent Rows

Dori_HarboringHearts · 05/03/22

I am trying to create a column for row number with parent rows only - so that child rows are not included in the count. For example, if the first row has 3 child rows, Row 1 would be #1 and Row 5 would be #2. Thanks in advance for the help!

Jeff Reisman · 05/04/22

https://community.smartsheet.com/discussion/comment/328285#Comment_328285

You can use any column that is to the right of the column that sets itself as the parent. In my example, "Tasks" is the primary column and the where the parent/child hierarchy is established. I added a column called "Other" and changed my formula to consider that column instead of "Tasks."

Jeff Reisman · 05/03/22

@Dori_HarboringHearts

I haven't found a super-easy way to do this, but here's one that works. You will need to add two helper columns to make this work.

First, add an Auto-number system column, just start it at 1, no letters or leading zeros, it needs to be a numeric value. I called mine "Auto."

Next, add a text/number field to extract the row number for just the parent rows. I called mine "Is Parent". In this column I used this formula:

=IF(COUNT(DESCENDANTS(Tasks@row)) > 0, Auto@row)

In English: If the count of descendants of the Tasks cell on this row is greater than 0, then put the value from the Auto column in this cell. Basically, it will only do this for parent rows.

Next, I added a column for "RowID". In this column, I used the RANKEQ function to rank the values in the Is Parent column, to create a RowID that meets your criteria:

=IFERROR(RANKEQ([Is Parent]@row, [Is Parent]:[Is Parent], 1), "")

In English, find the rank of the Is Parent column on this row, compared to the rest of the Is Parent column. Since RANKEQ will throw an error if you use it on blank cells, we wrap the formula in IFERROR with blank quotes at the end. This tells the formula to replace any error messages with a blank field.

Finally, right click on the top row in the Is Parent column and select 'Convert to column formula' at the bottom of the context menu. Do the same for the RowID column. You can also hide the Auto and Is Parent columns if desired, and move the RowID to the left for aesthetic purposes.

Here's what it looks like:

Dori_HarboringHearts · 05/04/22

Thanks so much Jeff! I'll give it a try.

Dori_HarboringHearts · 05/04/22

Unfortunately because I do not have a business plan, the 'convert to formula column' is not an option. Is there a workaround to this?

Jeff Reisman · 05/04/22

https://community.smartsheet.com/discussion/comment/328255#Comment_328255

Just copy the formula (Crtl-C / Crtl-V) to the rest of the column, or drag it down. The formulas are written using "@row" so that they will not change (nor will they need to change) no matter what row they are on.

Genevieve P. · 05/04/22

https://community.smartsheet.com/discussion/comment/328255#Comment_328255

Hi @Dori_HarboringHearts

Column Formulas are available to all plan types, including Pro. To convert a formula into a Column Formula, right-click on a cell. It will be the final option in the dropdown list!

Screen Shot 2022-05-04 at 2.58.56 PM.png

Cheers,

Genevieve

Dori_HarboringHearts · 05/04/22

@Genevieve P. thanks but this is what I see..

Jeff Reisman · 05/04/22

https://community.smartsheet.com/discussion/comment/328265#Comment_328265

You DO have to be licensed user! Do you have a licensed user on your account that could help you out? The Owner of the sheet or an Admin?

Genevieve P. · 05/04/22

Hi @Dori_HarboringHearts

My apologies, I should have clarified. Column Formulas are available for all current plan types (Pro, Business, or Enterprise).

Based on this screen capture it looks like you may be on a discontinued plan that doesn't have access to newer features such as Column Formulas.

In this case, I agree with the suggestion of using drag-fill:

https://community.smartsheet.com/discussion/comment/328259#Comment_328259

Cheers,

Genevieve

Dori_HarboringHearts · 05/04/22

@Jeff Reisman thanks again for your help - I think I figured out why my formula is not working. The primary column, which for me is [Patient Last Name] only has text if it is a parent row. Because the primary column for child rows is blank, it is not counting the descendants as greater than 0. My "is Parent" column is therefore blank. Is there a way to convert the formula to "if not blank"? This would also allow us to count the rows that do not have child rows associated.

Jeff Reisman · 05/04/22

https://community.smartsheet.com/discussion/comment/328285#Comment_328285

You can use any column that is to the right of the column that sets itself as the parent. In my example, "Tasks" is the primary column and the where the parent/child hierarchy is established. I added a column called "Other" and changed my formula to consider that column instead of "Tasks."

Dori_HarboringHearts · 05/04/22

@Jeff Reisman chose another column and it now works beautifully! Thank you!!

Sherry Fox · 06/14/23

https://community.smartsheet.com/discussion/comment/328153#Comment_328153

@Jeff Reisman ,

I had a need to do this EXACT thing. I followed your instructions but I got the #UNPARASABLE error in the RowID Column. My formula here was:

=IFERROR(RANKEQ([Is Parent]@row, [Is Parent]:[Is Parent], 1), "")

Same as you said to use. My goal is to be able to replace the manual data entry area (orange highlighted area with black text) with an INDEX/MATCH formula that would reference a list of release numbers on another sheet. Herre is a screenshot, and the "step" numbers represent the numbers that should appear in this column based on the parent rows, which are highlighted in gray. What did I do wrong???

Jeff Reisman · 06/15/23

@Sherry Fox You're going to kick yourself... but I think you're just missing the question mark that's in your column name!

=IFERROR(RANKEQ([Is Parent?]@row, [Is Parent?]:[Is Parent?], 1), "")

Sherry Fox · 06/15/23

@Jeff Reisman ,

All I can tell you is that I am blonde! LOL Thanks so very much! I have spent days trying to figure out how to accomplish this task before I stumbled onto this post. Thanks so very much!!!

Jeff Reisman · 06/15/23

@Sherry Fox 😂 It happens to all of us from time to time!

Numbering Parent Rows

Best Answer

Answers

Help Article Resources

Categories