Is there a way to find the last value in a column? Tried Index but did not work

Tony DiPaolo

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.

Paul Newcome

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.

Paul Newcome

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?

Tony DiPaolo

Correct, I want the 0 reported.

Tony.

Tony DiPaolo

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?

Paul Newcome

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, <> ""))

Tony DiPaolo

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.

Paul Newcome

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.

Tony DiPaolo

It works! Thank you very much, Paul. I really appreciate your help.

Tony.

Paul Newcome

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.