Unable to create absolute reference to a cell in smartsheet.

juanmartos

Hello,

I am trying to create an absolute reference to the cell Test1 for example, where Test is the column and 1 is the row. In order, to make the absolute reference I add $ in front of the column name and in front of the row number, $Test$1. However, whenever I, for example, insert a row above that cell or sort the values, the absolute reference value changes to the new position of that cell, however I want the reference to that cell to stay as $Test$1.

Some additional contextual details. I am trying to do this in the summary fields, to have a summary that will simply store the most updated row so I can then create a metric in the dashboard that will be updated with this summary fields.

Best,

Juan

Genevieve P.

Hi @juanmartos

There currently isn't a way for a Smartsheet formula to lock to the top row versus a specific cell. Absolute References will stay stuck to the cell you've selected, even if that cell moves location, as you've found.

What I would suggest doing is have a system column, Created Date, in your sheet. Then you can use the MAX function to find the oldest date and pull the value from the column you want based on this new row... a formula like this:

=INDEX(Test:Test, MATCH(MAX([Created Date]:[Created Date]), [Created Date]:[Created Date], 0)

Cheers,

Genevieve