Say I have a Date/Time [Date] column and a numerical [Value] column. I am creating a calculated column. I need a formula that will find the row with the most recent date in the [Date] column, then return the data in its [Value] column. Is this possible in SharePoint 2007?
EDIT: My requirements (sorry for the long read)
I have 8 KPI measures that are updated monthly. Currently I'm storing these in a list, with the 8 measures as 8 columns. I also have a date column and a month column that is calculated from that date column. I need to display the latest record from this list in a KPI list (where I will set the boundaries for the indicator colours, etc.)
What I thought would solve it would be creating a 'Latest' view of the list that has the values for the measures for each month, and then limiting the number of entries returned to 1 item and sorting by date. This does indeed display only the latest record.
However, when I create an 'Indicator using data in SharePoint list', set the view to this new 'Latest' view, and go 'Calculation using all list items in the view' -> Sum, it sums over all the items, instead of the ones in the 'Latest' view.
Is my only option likely to be using custom code, or can my current method be fixed somehow?