Pulling the last 50 results from long database?
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?
Help Article Resources
Check out the Formula Handbook template!