How to identify row number in a sheet where new rows will be added

hiteshwadhwa123 · 05/12/23

I want to create a sequence of 1, 2, 3, etc. in a column, but challenge is new rows will be added in this sheet. So, when a new row is added, the top row must have value of 1, followed by 2, 3, 4 for other rows in the column... wish if there was a formula like ROW(), like we've in excel.

Any suggestions how to accomplish this in Smartsheet, when new rows are coming in the sheet?

=Chris Palmer · 05/12/23

Hello @hiteshwadhwa123

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.

hiteshwadhwa123 · 05/12/23

Hi Chris,

This is a good formula, I get the serial # like 1, 2, 3 etc. in the column.

However, I'm not able to apply it as a column formula, which is needed because new rows will be added to the sheet, and we will want formula updates as per the new rows. This is the most challenging part of all this.

In excel, ROW() takes care of it.

Hitesh

Paul Newcome · 05/13/23

The above does work as long as the new row inserted has at least two rows with the formula in it above and/or below it. The built in auto-fill feature should pull it in.

A column formula version requires an auto-number column (called "Auto" in this example) and this would go inn the column that you want to hours the row number in:

=MATCH(Auto@row, Auto:Auto, 0)

How to identify row number in a sheet where new rows will be added

Answers

Help Article Resources

Categories