Since last Weds... I've been working on making a working prototype spreadsheet for a company I build/fix computers for. I almost never do this type of work for others... but they've been a great client and more importantly... they've been needing this done probably for years.
They gather environmental impact data and create reports for oil well companies that gets reported to the State. What I've been working with is data for one company in one area. In this case... currently they gather the data for 65 different oil wells and put it into 7 different spreadsheets. Then they take that data and manually create one or two graphs from it for their report for each oil well. Each time they add data... they got to manually update each graph ( or graphs ) for each of the 65 wells. Basically... it's a logistics nightmare keeping it all up to date ( or even knowing which spreadsheet they need to update ). Now multiply that by each company and area they work with...
On my prototype... I've converted the way they were keeping the data for only two of the spreadsheets and put all of it into just one big database table on one spreadsheet. From that one database list... I've made a single graph that can be used for all of the 65 wells ( instead of having one static for each well ) by just selecting the well they want a graph for with a simple drop down list. All of the data graphing is now dynamic and made on the fly. If they like my implementation... I'll convert the remaining data from the other spreadsheets they update into the database sheet I've made... make their other graph they need for their reports and then all they have to do is input the data into one place ( the database I made ) and they can dynamically create the one or two graphs they need for each of the 65 oil wells.
Here's a screenshot of the resulting working prototype graph I've got for now... This is just showing the depth to ground water in the oil well and the amount of benzene their tests have measured. You can see an incident that happened in around 2001 and also how well their filtering got later in around 2010. The less benzene they find in the water in the oil well itself... the less chance of it leaching into the actual aquifer that drinking water wells in the area pull their water from. They actually test for and track more than just benzene, they just use that one for the reference graph to monitor all of them. If they find concentrations that are too high with that, then they can refer to the more detailed report for the others and to see their levels.