Auto-Select Data Source for Chart

**Sirrip** · 09-01-2011, 04:41 PM

I have a chart on sheet2 that pulls data from a table on sheet1 that is updated monthly.
(See example attachment. I cannot attach the real file)

In the real file, the data table is populated automatically at the end of each month. So, at the end of September, data will be in that column.
My problem is that I need to get the chart to expand its data source to include September columns as soon as the month is over.
i.e. Notice the chart only has July and August data... I want to use VBA to have the chart's data source include the September column data automatically on October 1st.

Thanks!

**ChemistB** · 09-01-2011, 04:50 PM

Are those other months there on your datasheet but showing zeros or are they not there until that data is imported? You should be able to do this with dynamic named ranges and not VBA.

Edit: Added your modified spreadsheet with dynamic names linked to the chart. Add data and check chart. Is this what you are looking for?

**Sirrip** · 09-01-2011, 05:27 PM

The zeros are just placeholders until real data comes in at the end of the month.

What are dynamic named ranges?

In as simple terms as I can use: When I right-click my chart and choose "Select Data...", the marching ants around the data source table should expand one column to the right to envelope the September column as soon as the end of the month comes.

Thank you!!

**ChemistB** · 09-01-2011, 05:35 PM

See this link for Dynamic named ranges. Did you look at my attached file? Add data and see how the chart changes automatically.

http://www.contextures.com/xlNames01.html#Dynamic

**Sirrip** · 09-01-2011, 06:01 PM

Chemist,
I did open it. But forgive me, I'm not sure what exactly you changed or how dynamic name ranges work.

Thanks again!

**ChemistB** · 09-02-2011, 09:48 AM

Okay,
In Excel 2003, Go to the Insert>Name>Define. That will pull up a list of defined names in that spreadsheet that I set up as per the link from above. Read through the link to understand how these dynamic names work. Basically they are counting values greater than zero in the Product a row and increasing the range of all the named ranges every time that increases.

If you then go to the chart and right click on it and "Select data", you'll see how each series is set up in the chart. I manually entered each one. I hope that helps.