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!
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."
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!
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:
Thanks so much Jeff! I'll give it a try.
Unfortunately because I do not have a business plan, the 'convert to formula column' is not an option. Is there a workaround to this?
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.
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!
@Genevieve P. thanks but this is what I see..
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?
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:
@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 chose another column and it now works beautifully! Thank you!!
@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:
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???
Project Analyst | Core Quality Services (QMS Transformation)
EAP | Mobilizer | Automagician | Superstar | Community Champion
@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), "")
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 😂 It happens to all of us from time to time!
Create and edit formulas in Smartsheet
Formula combinations for cross sheet references
Smartsheet functions list
I created a timesheet and a form to go along with it. When someone uses the form and flags that someone has called off, I want the flag to return zeros in the ST Hrs, OT Hrs, and DT Hrs cells. Using AI to generate the formula below, it returns a Circular Reference error when the formula is placed in the ST Hrs cell.
Hi everyone. Some help please. I've been through index match but I don't think it's what I want. What I want to do is... Someone submits a form with one of the fields being "sub job", selected from a dropdown. Once I go into the spreadsheet after the form is submitted, I want the adjacent "WD no" column to be automatically…
Hi there, I'm building out a new sheet where I utilizing the system's "Last Modified" date column. I tried to trigger a workflow off of this column that sends an update reminder if it hasn't been modified in 2 days, but the column is not showing up as an option (presumably because it's system generated). With that in mind,…
©2024. All Rights Reserved Smartsheet Inc.