Charts auto expand source data

[cheesemeister]

Hi all,
I have a group of charts that expand the range they pull data from once a week.
Is there any way to make them do this automatically when new data is entered into the next set of cells?
Failing that is it possible to use a macro to automate updating the ranges, tried it with the recorder, didn't get very far, it recorded everything to do with selecting the graphs, as soon as I right clicked, it ignored it completely until I was back out of the menu and selecting the next one.
Cheers mucly

[TMS]

You can create a Dynamic Named Range for each of the axes. Then use that as the Series data.

You will probably need to post a sample workbook so that this can be demonstrated.


Regards, TMS

[cheesemeister]

I know there's duplication on every single thing in the data sheet (2), this is what the people who use the file want...
graphs for dynamic range.xlsx
The graphs currently pull their data from the right place, I just want a way to make it automatically expand by one column each time new data is entered.
I will eventually be given a new set of data for the targets section, when they decide on something that's reasonable this time.
Thanks muchly.

[stasinek]

Or using the PT - all in one

[cheesemeister]

As I said the people who use the file have dictated that it's how they want it, so I can't play with the format, layout etc or use pivot tables.

I tried adding a new week's worth of data to the file above, both on my original data sheet on tab 2, and the new long table on the left of the new tab 3. nothing happened, either to my original graphs, or the new ones. Also the pivot tables don't see the new data either.

All I need to do is add a new set of data each week and have the existing graphs automatically expand to include it. If that means I need to create new graphs so be it, but they need to look and be arranged as close to how they are now as possible.

[stasinek]

See if now is OK

[cheesemeister]

Works like a charm
Thanks muchly.
Now to figure out how you did it, and replicate it across a bunch of different files.

[humdingaling]

if you are having issues with doing the offset method you can also try the table method which updates when the table gets updated

http://www.techrepublic.com/blog/mic...arts-in-excel/
this site has both methods listed