Force auto fill formula to increment from the top of sheet

Darren Mullen

Hi,

I have a sheet with project workflows. Each workflow has an ID# and they have indented rows. I have a form that enters new rows into the top of the sheet. Everything is working fine as far as auto-filling all the gnarly formulas I have in various cells, except I have one issue:

Currently, I have to assign the ID# manually. I'd like it to auto increment, but I can't seem to get a formula to work correctly. Auto increment isn't appropriate in this application because child rows are added at various times and I don't want child rows to have ID's assigned, only the new parent rows.

A formula such as =ID2+1 works as long as no child rows are inserted. I'm not sure if there is a way to do this...

Below is an example sheet of the structure. I would like the next row inserted to be "6" under the ID column

http://publish.smartsheet.com/882377d17f564798b2a286eb85e8650a

Alejandra

Hi Darren,

I accomplished this with the formula below and with the help of the Auto Number System column ("Row ID"):

=IF(COUNT(CHILDREN([Primary Column]@row)) > 0, [Row ID]@row, "")

The formula counts the number of children rows and returns the Row ID (generated by the Auto Number column) if the number of children rows is greater than 0 (making it a parent row).

Darren Mullen

Alejandra,

Thank you for the answer. However, I was looking to have the parent rows only be sequential and not skip numbers due to the child rows. I did come up with a solution yesterday where I generate the auto ID on a separate sheet when I copy new parent rows through an automation, then I use the match and index function in combination with unique data in the row to find the auto ID in the separate sheet.

Eric M Oliveira

Hi Darren,

From the description provided it sounds like you'd only like Auto-Number to pertain to certain rows, some Parents that have children and others that are project rows that may not have children rows. Currently, we don’t have a method to specify which rows Auto-Number fills but this will be considered as a possibility for future development. Utilizing a formula you may be able to produce a unique value for the row utilizing values of the row and a JOIN function. Note this may or may not be numeric values but more of a unique ID such as "Project - A".

Another method is that which you have described above where customers will take another sheet have an AutoNumber column and utilize an INDEX(MATCH()) to pull the ID from the other sheet to populate the desired Rows. In not knowing how you're matching the AutoNumber you may be able to set up an Automation on your original sheet to copy row to the other sheet when it meets certain criteria. This could then generate an AutoNumber and then in turn Automatically update your original sheet with the ID utilizing the INDEX(MATCH()).

Have a wonderful day. Thank you for contacting Smartsheet Support.

Cheers,

Eric

Smartsheet Technical Support

Paul Newcome

Do you need it to be

5

4

3

2

1

or will it work for you if it is the other way around?

I have a few ideas that need some testing on how to make it work the way you currently have it, but I am not sure if they will work or not. If you are ok with it being the other way around, the formula would actually be pretty straightforward.

Paul Newcome

Ok. I have a solution, but it requires 2 additional columns and an extra row at the top of the sheet. For this to work, the top row would need to stay in place. Basically your data would actually start on row 2 and any new rows inserted would be inserted there.

First helper column I called "Hierarchy" and used this formula in row 1:

=COUNT(ANCESTORS([Workflow Name]@row))

Second helper column I called "First Count" and used this one:

=IF(Hierarchy@row = 0, COUNTIFS(Hierarchy$1:Hierarchy@row, 0)) - 1

Then in the ID column I used:

=IFERROR(IF(Hierarchy@row = 0, LARGE([First Count]:[First Count], [First Count]@row)), "")

Enter all of those formulas on row 1 and dragfill down. Then insert new rows above row 2 instead of above row 1. You could even use helper text in row 1 of the Workflow Name column that says something along the lines of "Insert new rows below." so that users know to leave that row in place.

EDIT: You can ignore the Task Numbers in the Workflow Name column. I did 1, 2, 3, 4 and their sub-tasks just to start with then tested with 5 and 6. The ID numbers are in the correct order, I just didn't start the sheet the best way for this build.

Darren Mullen

Thank you all for the comments.

Eric,

I am using a separate sheet with auto numbering and doing exactly what you are saying.

Paul,

I considered a solution similar to you are proposing, however, using a separate sheet and pulling the auto number back into the main sheet works best for my application.

Paul Newcome

Happy to help! 👍️