Unable to create absolute reference to a cell in smartsheet.
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
Answers
-
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
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.7K Get Help
- 371 Global Discussions
- 203 Industry Talk
- 436 Announcements
- 4.5K Ideas & Feature Requests
- 137 Brandfolder
- 129 Just for fun
- 129 Community Job Board
- 448 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 282 Events
- 32 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!