Autogenerate IDs built off of other IDs?

pzupke

Hello! I'm trying to auto-generate IDs that build off of another ID. The formula I'm currently using is:

=[Initial ID]@row + "-R" + COUNTIF([Initial ID]:[Initial ID], [Initial ID]@row)

I would ideally have it set to:

=[Initial ID]@row + "-R" + COUNTIF([Initial ID]1:[Initial ID]@row, [Initial ID]@row)

But that's not allowed in Smartsheet at the moment. Is there any way to run this so the IDs get numbered in order of being added and stay that way, rather than changing as more are added?

This picture hopefully demonstrates my issue. I want them to count up and have old ones not update.

Thanks!

Paul Newcome

It is possible using the last formula in your post, but it would have to be dragfilled to populate the existing rows (make sure that 1 is locked in as an absolute reference), and then the auto-populate feature will pull the formula in as long as there are at least two rows above and/or below the new row.

To get it set as a column formula, you would need to insert a text/number column (called "Row" in this example) and use this:

=MATCH([Initial ID]@row, [Initial ID]:[Initial ID], 0)

Then adjust your COUTNIFS to include this helper column like so:

COUNTIFS(.............................., Row:Row, @cell<= Row@row)

pzupke

Thanks for the response, Paul!

I was able to get the first suggestion to work, my only concern is that all of these existing rows are actually just for testing. Ideally it would start over from scratch. I suppose I could put in two dummy rows and hide them for the official release.

I'm struggling to get the second suggestion to work. It is giving the same results as my first attempt with the formula:

=[Initial ID]@row + "-R" + COUNTIFS([Initial ID]:[Initial ID], [Initial ID]@row, Row:Row, @cell <= Row@row)

The "Row" formula is exactly as you typed it.

If you are able to explain that second method I'm very open to learning more, but I at the very least have one option now, so thank you very much!

Paul Newcome

I thought your Initial ID was a system generated Auto-Number column. It appears I was mistaken. Since that is the case, insert an auto-number column (called "Auto" in this example) and adjust the Row column formula to match on the auto-number column.

pzupke

@Paul Newcome that did the trick! Yeah, the "Initial ID" column is entered into the sheet via form, but it is based on an auto-generated number on another sheet that we use to link other data. Thank you so much!

Paul Newcome

Happy to help. 👍️