Changing what data is viewed in multiple EXCEL charts

[rookiesql]

My problem is the following:
I have a big matix of data with the folowing structure:

Jan Feb Mar ...
data1 ... ... ... ...
data2 ... ... ... ...
data3 ... ... ... ...
....

From this matrix I have generated a big number of charts
with different data vs. the months, i.e. months on the X-
axis.
Now I want to change the period of time I show in all
charts for instance from Jan - Nov to Feb-Dec. How can I
do that in a smart way, without editing each and every
chart? Using some kind of variable? Have anyone done such
a construction?
Regards
Anders

 

[xlbo]

Depends how you want to go about it.....button with code or manual process. The easiest way would be to make the charts look at visible cells only and then hide the columns for those months that should not be plotted...

Rgds, Geoff
[blue]Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?[/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[rookiesql]

OK, the hide columns approach works, thanks for that!

However, another problem is when I add the data for another month at the right of the matrix. How can I modify all my charts (maybe 50 of them) to show also the new months data, without editing the selection of source data for each and every chart. One month at the time is added and every time I have had to edit each chart (select a data area which is 1 month wider). There must be a better way!!!
Thanks,
Anders

 

[xlbo]

How about setting up a dynamic range name:
If data starts in A1 on a sheet called ChtData then go
Insert>Name>Define
Enter a name and instead of a range ref, enter:
=OFFSET(ChtData!$A$1,0,0,COUNTA(ChtData!$A:$A),COUNTA(ChtData!$1:$1))

This will create a range name that expands as you add data
Simply reference your charts to the range name rather than cells refs and thunderbirds should be go

Rgds, Geoff
[blue]Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?[/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[rookiesql]

Ok, I understand the concept of using a name and that the suggestion creates a reference to the complete data matrix. Unfortunately in my case it is more complex. A typical source data for my charts look like this:

=sheet1!$A$1:$U$1;sheet1!$A$3:$U$3;sheet1!$A$6:$U$6;sheet1!$A$10:$U$10

...and then I have 5 other charts in which I display other rows of data in the matrix
...and then I have 6 other sheets where I have the data for other products.

What I need to do when adding another month of data (another column to the right in the data matrices) is to exchange the $U$ with $V$ in all the source data definitions for all the charts. I tried to use a defined name in the source data definitions, but couldn't understand what syntax to use (if possible at all).

Any solution?

 

[xlbo]

Ouch....Rod......for your back......making
I'm not having a go but I'd seriously suggest sorting out your data layout

Other than that, the only thing I can think of at the mo would be to loop through the series collections and return the address strings - then look for
":$" and find the next character. that'll give you the current column
Then, use a lookup table to get the next column and do a find / replace in the address string and repoint the series collection to that......not nice

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244