Formula to determine row number

Fred Meissner

Hello,

 

I am wondering if there is a way to determine the row number of a specific row.  I know this can be done in Excel with the row() function.  This does not work in Smartsheet.  The reason I am looking for it is because I have a formula that needs to apply to every row that checks the row above it, but that fails when it is in the first row.  I can't just have a static first row because the entries move around based on priority.

 

If there is a way to determine the row, the formula can check if it is in the first row, and then act accordingly.

 

Thanks,

Fred

J. Craig Williams

Fred,

 

There isn't.

 

I use a simple column [RowID] with the value 1 in row 1 and this formula in row 2:

 

=[RowID]1 + 1

 

I then copy that to the rest of the column.

 

It breaks when rows are added or deleted. Copy-drag fixes that.

I only keep it updated when I am sorting or working with dependencies - I make the font BIG AND BOLD so I can see it easier than the greyed-out row ID.

 

Craig

 

Rob Hagan

Hi Fred,

 

There is, provided that you are OK using two columns to achieve the outcome.

 

Let's assume that we have two columns at our disposal named Column2 and Column3.

 

In the first row of Column2 put the formula =1+0 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.

 

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

 

Cheers,

 

Rob.

Fred Meissner

Thank you Craig and Rob.  Both are reasonable solutions, though Rob's is more robust.

 

However, in my situation, it doesn't help as I thought it would.  The IF statement that checks if it is in row 1 still gives me the #UNPARSEABLE error in the first row, because there is no cell above the first to reference.  The ability to reference a non-existent row is not dependent on the IF statement.  I should have seen this coming...

 

The only other way I can think to solve this would be to use a cell value as the row number, but this doesn't seem to be possible in Smartsheet.

 

Thank you for your help!

 

Fred

J. Craig Williams

Brilliant Rob.

Looks like I have something for my pathfinder list. 

 

Fred, 

If you are using Fred's method (and you should) and still getting #UNPARSEABLE then you are speaking about another formula somewhere else.

 

Use either

 

=IF([Column3]1 = 1, (do stuff because you are on row #1), (do other stuff))

 

and copy those down in your column.

 

or

 

=IFERROR((do stuff you want to do), "error - hmm, might be on Row 1")

 

That should fix you right up.

 

Craig

 

 

J. Craig Williams

Shaine,

 

Please mark Rob's answer as the Best Answer.

Very much wish I had thought of that.

 

Craig

Ezra

To piggy-back on this.. I had wanted a clean way to make every-other column a grey background. I just wanted a 0 or 1 to provide a conditional formatting rule some meat to chew on. Instead of two rows, I'm using a [Data] column (assuming that I'll always have information in the Data column).. and a [Style]

=MOD(COUNT([Data]$1:[Data]@row), 2)

then I wanted to make a distinction between indented row levels and the parents...

=MOD(COUNT([Data]$1:[Data]@row), 2) + IF(COUNT(ANCESTORS()) = 0, 1, 0) + IF(COUNT(ANCESTORS()) = 1, 10, 0) + IF(COUNT(ANCESTORS()) = 2, 20, 0) + IF(COUNT(ANCESTORS()) = 3, 30, 0) + IF(COUNT(ANCESTORS()) = 4, 40, 0) + IF(COUNT(CHILDREN([Data]@row)) > 0, 5, 0)

......the last part needs to be looking at something other than COUNT(CHILDREN()) or you get circular ref errors

Paul Newcome

Stumbled across this looking for something completely unrelated and figured I'd put my two cents in. My solution is very similar to Rob's except I only use one helper column and any other column that's on the sheet.

 

The helper column formula would be

 

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

.

You can use this to look at any column you want that's already on the sheet, and it will count from row 1 to the current row how many cells within the range are either blank or not blank.

Andrée Starå

Excellent! 

It will probably work for my TOC solution and more.

Thanks!

Paul Newcome

That's actually how I am using it. As a sort of TOC at the top of my Project Plans. I have two helper columns. One that pulls the row number for each parent task, and the other pulls the task name.

 

I then collect all of the At Risk Tasks with their column numbers and swapped out my workaround for a line break with the new CHAR(10). 

 

If none are at risk, the cell at the top just says "On Track. No Deliverables At Risk."

 

If one or more are at risk, It changes to provide instructions on how to use CTRL + G to jump to a specific row, and lists out each row number/parent task that is at risk kind of like so...

 

"Use Ctrl+G and enter the row number yada yada yada"

3: Task Name on Row 3

28: task Name on Row 28

487: Task Name on Row 487

.

Once it is no longer at risk, it disappears from the list. Anything else that becomes at risk is automatically added to the list in numerical order.