Sorting data for charts and future updating

**Casper9T9** · 04-03-2009, 10:47 AM

I maintain relatively large amounts of data that we split and analyse via charts to identify pricing trends. I've attached a file as an example. Typically I have a list where new information is added as it becomes available. I have a rather archaic way of charting this info. I will sort the list in the Data Input worksheet the way I want and then copy and paste that selection to the Chart Info worksheet. I will then chart that selection from the Chart Info worksheet.

The obvious problem with this method is that if I add new information, revise old information, or add a new column, the data I copied into the Chart Info worksheet becomes outdated or just plain wrong. It becomes an arduous task to correct this since the chart information is not linked to the main database.

I'm seeking suggestions on ways to resolve this problem. Basically if I add new information to the database or revise old data, I want the charts to automatically reflect this. For example, if I receive 2007 pricing data, I want the Supplier A chart to pick up this information without me copy and pasting, selecting the range for the chart, etc.

Whatever the recommendation or solution, it needs to be flexible so we can easily add new charts while maintaining old ones as we gather more data, add more columns for calculations, or revise legacy information. Your help is appreciated.

**Andy Pope** · 04-05-2009, 10:06 AM

I don't think there is any simple solution to your problem.

At first glance pivot tables look like a good candiate to help summarise your data but you can not create xy-scatter plots directly from PTs.

Formula could be used to create a column of values for a set of given conditions and then the charts could be based on these ranges. This will require the use of dynamic named ranges to keep the charts updated. But the draw back is you have to build and maintain the formula. Not so bad setting up but adding new items could be a chore.

another approach could be to construct code to summarize the data, possibly using the auto filter approach you are currently using. The charts can also have there range references updated when the code runs.