How can I set a Row ID column that does not change when you move, add or delete rows

mclawrence_123

Hi all -

I've been able to create a Row ID column that updates when you add/delete a row fairly well, but when rows are moved around it breaks again.

Current set up:

X: autonumber column

Row ID: =MATCH(X@row, X:X, 0)

This works ok but every time I move rows or make big changes I have to re-set the autonumber (X) column. I just need a column that mimics the row# that smartsheet already provides (1st column before the primary)

Thanks!

=Chris Palmer

Hello @mclawrence_123 I found this formula from Paul Newcome that works great for this:

=COUNTIFS([Column Name]$1:[Column Name]@row, OR(ISBLANK(@cell), NOT(ISBLANK(@cell))))

Update "Column Name" with the primary column in your sheet.

mclawrence_123

Hi @Mr. Chris thank you for this! Are you able to make this a column formula? It isn't letting me

Ken Armstrong

@mclawrence_123 In order to make this wonderful formula a column formula you have to replace all @cell with @row.

Eric Curtiss

So if my primary was called row the column formula would be

=COUNTIFS(Row$1:Row@row, OR(ISBLANK(@row ), NOT(ISBLANK(@row )))) this doesn't seem be working

Paul Newcome

Your Row ID column should update just fine without having to reset your auto-number column using the method outlined in your original post.

Paul Newcome

The reason the COUNTIFS method cannot be made into a column formula because of the absolute references ($ before a column name or row number). Leaving "@row" as a stand-alone point in a formula such as ISBLANK(@row) will break a formula every time. The "@cell" references should stay as is. The COUNTIFS method was derived before column formulas became a thing and has a number of issues with it. I recommend (and consistently use) the method outlined in the original post with no issues.

The only "issue" you may encounter is that the auto-number column does not populate until the sheet is saved. So if you inserted a bunch of rows without saving, the [Row ID] column will appear broken, but if you save the sheet, it should correct itself.