Pulling the last 50 results from long database?

Graham Rimell

I've got pages of test results (numerical values) that go back years that need displaying on a graph. New results get recorded every week. I'm after a way of making a chart that automatically updates to the latest x number of results (A year of results probably).

The way the graphs work, they look at a fix range of cells and have no function for automatically updating the range of data. I don't want to have to manually update 50 or so graphs every week to look at the extra row of data, so I'm having to work around it.

I've used a formula that pull up the most recent single entry. Which has been useful, but I'm after a way of pulling the last 50 results and having them in a fixed cell range, so the data in the cells change rather than the range of cells that the graph is looking at. The formula would need to be something that looks at the latest results, and counts x many times backwards.

This is the lookup I used before.

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

Does anyone know how I do this? If it's even possible?

Alternatively, is there a better way to make an graph look at the most recent portion of a range of data without needing to manually modify the dataset range?

Thanks.

Paul Newcome

How is new data added and how frequently?

Jim Hook

Graham, I have a similar situation with archive sheets that store one or more records weekly using automated workflows. One in particular is used to store certain statistics weekly using one row (record) per week as part to provide data for a chart spanning the last 52 weeks. I have the additional problem in that occasionally I archive the same week data more than once when a correction is made. I ended up creating what I call "smart archives" that have additional columns to the right of the archived data that do processing on the archived data to eliminate duplicate records, just keeping the last one archived for a given week, plus occasionally performing additional analysis like four or 13 week moving averages to smooth out the data for charts. In one case another sheet that generates data for charts going back one year looks at the processed data from the archive and retrieves the last valid record number in the archive using the MAX() function. Once it knows that record number it easily retrieves the last 52 valid records for the chart.

It was quite challenging to get this to work at first but I've done it several times now with good success. The process is too long to describe in more detail here but I could share more detail in an email if you're interested and have experience using many of the advanced functions in Smartsheet including IF(), SUMIFS(), COUNTIFS(), INDEX(), COLLECT() and MATCH() functions.

Graham Rimell

I've actually managed to solve this using the report functionality. I used the 'When' and the 'Is in the last (days)' box to filter by the last x many days.

Then you can set up a graph on a dashboard to look at that report and it updates automatically. It's pretty slick and avoids any complicated formulas. Shame I didn't know about it before because it's very useful.