There has got to be a better way! (Excel Charts for Lots of Data Question)

[doktorinjh]

I am trying to find a better way to produce graphs for ~100 monitoring wells. At the moment, I have each well in a separate tab in several worksheets (I take most of the blame, but my company/others have been collecting this data for almost a decade), which means that adding and displaying data is a mess. Each tab has it's own graph of the groundwater data and I have to export each graph individually in order to produce a PDF for report purposes.

The data for each well contains a date, a field reading, several formulas/columns that produces an elevation, and an adjacent pond reading. As a final product, I'd like to have a chart with a line graph with a date on the x-axis and an elevation on the y-axis, for the pond and the well.

Due to the various inputs, not every well is set up the same in each tab (eg, data fields aren't always in the same column and row across the tabs/worksheets).

So, can anyone give me some advice on how to best organize a ton of data that is scattered across many worksheets and help pull this inefficient cluster from the fire?

Thanks,
Jason

 

[easy2understandexcel]

Someone else my have a better idea, but Access will import excel data based on the column headers... so as long as your headers are all typed the same (they don't have to be in the same columns) you could import it all into Access, and then pump it back out to excel. Good luck.