Chart with dynamic CSV data source

Hello,

We have a reporting engine which generates an Excel file which contains CSV data on the first sheet. We would like to build an Excel chart on the second sheet which uses data from the first sheet. The hard requirement is that the chart should use all data available on the first sheet. In other words, if we programmatically add more data to the first sheet then the chart data source should automatically include the data without having the user change the data source range manually.

The solution can use Excel 2003, 2007 or 2010.

Maybe there is a way to define the chart's data source as a query over the first sheet?

Thanks!

Alec

Hi AlecSwan,

You can use named ranges with an offset formula to create your dynamic chart. This should allow you to chart ranges on one sheet to another. The offset formula can be set to count the values on the first sheet and graph the dynamic chart range.

Here is a tutorial:

http://www.exceldashboardtemplates.com/?p=688

Good luck!

Steve=True

My approach has been to select a larger range for the chart series than I ever expect to see from the CSV file. The unused "blank" cells are ignored by the chart.

I was able to build a dynamic range and chart thanks to a great reference by Steve=True.

I defined dynamic range as =OFFSET($B:$B,1,,COUNT($B:$B),). However, when I convert this range to Table (Excel 2007 feature) the table boundaries are hardcoded and no longer dynamic. How can I define a dynamic table over a dynamic range?

Thanks,

Alec