Reference Row Number?

I'm building a task management sheet that prioritizes tasks by their order on the sheet. A formula calculates lead time of a task based on its current position in the list. Is there a way to dynamically reference the row number of a task?

The easiest way would be a function that references the row number (since those update dynamically as a row is dragged to a new location), but this seems not to exist yet.

I have accomplished this in Excel by using the OFFSET function to look at the value of the cell directly above the current cell (=[previous cell] + 1), but Smartsheet doesn't have that either. In fact I can't find any way to reference a cell's location, its neighbors (apart from parents/children), or the current cell in general.

Has anybody found a way of dealing with this? Auto-numbering columns won't work because the cell values do not change once created. Also, I don't want to create a chain of parent/child relationships.

Find more posts tagged with

Formulas

Cell linking

Enhancement Request

Comments

Shaine Greenwood

You're correct in that aside from Parent/Children, there isn't a way to reference a cell location based on relation to the function. The closest I could imagine would be to use the $ symbol to make the cell reference absolute, so it always references the cell above it regardless of whether rows move or not. You can find details on Using Formulas in Smartsheet in our Help Center: http://help.smartsheet.com/articles/775363-using-formulas

I'll pass your feedback to our Product team for a way to reference based on relational location.

BMar

Thanks, Shaine! I'll have many uses for that if it is developed.

Please also ask about a way to reference the row number (from the draggable shaded cell on the left). That would be the easiest and most computationally efficient way to solve this specific problem.

Shaine Greenwood

Formulas rely on the row number as well as column name when referencing cells:

=[Column Name]1 + [Other Column]1

Considering this hypothetical formula, if I were to move row 1, the formula would dynamically update to the new row number. If I move the row containing the formula, it will still reference the expected row number.

What we don't yet have in Smartsheet is a way to reference the row above without manually typing in the column name and row number.

BMar

I think I found a way to accomplish what I need.

I created a hidden column called "index" in which every cell contains the value of 1. In a separate column called "Priority" I placed this formula:

=SUM(index$1:index1)

Drag the formula down to auto-fill the column and the last row number (shown in bold) increments for each row. Now when a row is dragged to a new position in the list the number in bold refreshes to reflect its new position.

Shaine Greenwood

Glad you found a solution!

Soren

The SmartsSheets have row numbers automatically, it's the first column -- followed by the system rows for actions, attachments, comments and locks; can we just reference the first system row for the row numbers that are already there? Creating an index row in addition to the system row seems redundant.

lily.olkkola55056

Has a Neighbor() function been made yet? Seeing as it has been 2 years since this problem was reported I can't find anywhere else online that solves the issue without going through the hidden index solution.

If not, then I suggest that you make it a hierarchy where it has to be reference inside a function to work. Here is the format I suggest:

Where if [Enter Number] is a positive value, the function will start from where the [Specific Cell] will go up the exact number of spaces that the [Enter Number] equals. If [Enter Number] is a negative value, it will go down from where the [Specific Cell] is [Enter Number] amount of times.

=FUNCTION(NEIGHBOR([Specific Cell], [Enter Number] ))

Another solution is to, instead of basing of whether the number is positive or negative to see which direction it goes, using a boolean instead. Where true is up and false is down.