# Numbering Parent Rows

Options
edited 06/16/23

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!

• ✭✭✭✭✭✭
Options

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

Regards,

Jeff Reisman

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

«1

• ✭✭✭✭✭✭
Options

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:

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:

Regards,

Jeff Reisman

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

• Options

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

• Options

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

• ✭✭✭✭✭✭
Options

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.

Regards,

Jeff Reisman

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

Options

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!

Cheers,

Genevieve

• Options

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

• ✭✭✭✭✭✭
Options

Regards,

Jeff Reisman

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

Options

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:

Cheers,

Genevieve

• Options

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

• ✭✭✭✭✭✭
Options

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

Regards,

Jeff Reisman

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

• Options

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

• ✭✭✭✭✭✭
Options

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

Sherry Fox

Project Analyst | Core Quality Services (QMS Transformation)

Medtronic

EAP | Mobilizer | Automagician | Superstar | Community Champion

• ✭✭✭✭✭✭
Options

@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), "")

Regards,

Jeff Reisman

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

• ✭✭✭✭✭✭
Options

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

Sherry Fox

Project Analyst | Core Quality Services (QMS Transformation)

Medtronic

EAP | Mobilizer | Automagician | Superstar | Community Champion

• ✭✭✭✭✭✭
Options

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

Regards,

Jeff Reisman

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

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!