Is there a way to find the last value in a column? Tried Index but did not work
Hello,
I am trying to get the latest values added in a range to generate the dashboard metrics I need. I am able to return the max values, but the values in one column (Change), may go down or up. I tried using INDEX, but it did not work.
Best Answer
-
I completely forgot. You can now use one instance of a circular reference in a column as long as we follow certain rules. If that is the only formula like that that you will be putting in the Change column, go ahead and put it in that last row.
Answers
-
Are you looking for the most recent? OS in the case of your screenshot you wan to return 0 because 4/24 is the most recent date filled out?
-
Correct, I want the 0 reported.
Tony.
-
Paul, I also thought of creating a lookup formula that looks at the most recent date with data in that column. Perhaps that is possible?
-
Where exactly are you putting the formula? If it is NOT in the Change column, you can use something along the lines of
=INDEX(Change:Change, COUNTIFS(Change:Change, <> ""))
-
Ah, I am putting it in the last row at the bottom (to the right of "Latest"). I would prefer to put all the latest data in one row so I can easily create my metrics by referring to that row. But I suppose I can put that one metric in a separate column. I can try.
-
I completely forgot. You can now use one instance of a circular reference in a column as long as we follow certain rules. If that is the only formula like that that you will be putting in the Change column, go ahead and put it in that last row.
-
It works! Thank you very much, Paul. I really appreciate your help.
Tony.
-
Great! Happy to help! 👍️
I am not sure exactly when that change was made, but we only discovered it in the past couple of months. It is so counter-intuitive and against everything we have always done that it is taking some getting used to.
This should continue to work so long as you don't replicate that formula in the same column again.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!