Row number

Rob Hagan

Hi Thrill Seekers,

I am seeking a way to access the row number of a row in a sheet. I would be very happy if this came in the form of a SmartSheet function (let's call it Row) so that I could create formulas like "=Row()" for the current row number or "=Row()+2" for the number of the row two rows below the current row. Alas, that function does not appear to be implemented in SmartSheet.

I have created a solution that does achieve the outcome, but as you will see it has a speed issue.

Let's assume that:

* we have two columns at our disposal named Column2 and Column3, and

* that Column2 has a value in every row (you probably have such a column in your sheet but if you don't the you can put the formula =1+0 in row 1 of Column 2 and then copy it down to the end of the used rows).

In the first row of Column3 put the formula =COUNT([Column2]$1:[Column2]1) and then copy it down to the end of the used rows.

Column 3 will now contain the row number which can be used in any location where you would have used the function Row().

This solution survives both the addition, the deletion and the copying of rows, provided that SmartSheet's auto-filling meets its contractual commitments.

BUT, this solution becomes slower and slower as the number of rows in the sheet increases (its computation is of "Order n squared" (where n is the number of rows in the sheet). In our context, and probably yours too, this makes this solution unusable.

So, the thrill here is to find a solution that is of "Order n".

Any assistance at all will be most appreciated.

Cheers,

Rob.

J. Craig Williams

Rob,

Unique values in any column, preferably no blank values in same column?

Most solutions don't survive sorting, is that an issue?

Craig

 

Rob Hagan

Craig,

The Column2 (or whichever column that you use as my Column2) doesn't need unique values, only no blank values. In our implementation, we have a [Row Type] column that we guarantee to have a value in every row so that is a valid candidate as a Column2. The =COUNT([Column2]$1:[Column2]1) formula is counting the non-blank items from the first row down to the current row, thus delivering the row number. Fundamentally, SmartSheet is happy understand the current row and an absolute row. In our implementation we mandate certain metadata content in row 1 that we then lock so that it can't be deleted or moved around. This gives us usable "Column2"'s.

A usable solution must survive sorting, inserting, deleting and copying of rows. So, yes, a solution that doesn't survive all of these actions would be an issue.

Cheers,

Rob.

J. Craig Williams

I think my solution requires unique values. I'm still testing. This is all internal Smartsheet - I want a  non-third party, non-API solution.

But ... it survives all of the other conditions.

Craig

 

Sterling Crawford

As long as you have a column with a unique value per row, this is your best bet. It gives you the row number and it automatically updates if you move rows up/down or insert new rows:

=MATCH([Column with unique values per row]@row, [Column with unique values per row]:[Column with unique values per row], 0)

This is also able to be set as a column formula now that Smartsheet has rolled that feature out.

Rob Hagan

Hi Sterling,

I really appreciate your reply, as it is still an open issue for us. Yours is an elegant solution as it survives sorting, deletion and addition.

The challenge is that I don't have a column with guaranteed unique values. I have a number of columns that come very close, but close enough is not good enough for a stable system - pity.

The column formula functionality is one of the best additions to SmartSheet in recent times IMHO. It has removed a major source of errors and has simplified the injection of "templated sets of tasks" into our production workbooks.

Thanks again,

Rob.

Annie R.

Thank you, Sterling! V helpful and I agree with Rob - yours is an elegant solution.

Rob, you can guarantee unique values using an Auto-Number column. I've set it up and works perfect.

~Annie~

Rob Hagan

Hi Annie,

Does your solution survive sorting, inserting, deleting and copying of rows? If so, then I'll be a Happy Camper!

Cheers,

Rob.