Multiple Charts :: Change Data Series each month

[Topcat04]

Hi There

I am after a best practice solution please.

I have 25 graphs in a dashboard report. Each month, new data is added and I need to expand out the data series to include the new column reference with the new data.

Eg a1:m1
Next month it would move to
a1:n1

Im sure there is a best practice way of doing this where the user does not need to go into each graph each month and change the data series for the plot and the x axis data range?

Thank you for your help,

 

[xlhelp]

http://peltiertech.com/Excel/Charts/Dynamics.html

http://www.tushar-mehta.com/excel/newsgroups/dynamic_charts/index.html

A man has only two choices: He can be right or he can be happy.

 

[Topcat04]

Thank you.

 

[SkipVought]

Hi,

Make a Dynamic Named Range. faq68-1331

If your Named Range for your dates, for instance, were Dte on Sheet1, then your Series reference would be
[tt]
=Sheet1!Dte
[/tt]
Each time you add a row, Dte recalculates and is reflected in the chart. Do the same for each y-axis series.

Just out of curiosity, why do you have 25 charts?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Topcat04]

Hi Skip

Thanks for the reply. I have 25 different products that we plot results for each month. they are laid out on a 1 page print out sheet for easy viewing.

thanks

 

[Topcat04]

XLhelp

The links you pointed me to show how to create a dynamic range which is great, thanks. However, looking at it, I would need to create 25 ranges and still update them each month?

is there a way where I could use one cell as a reference point, eg M1 and have the second part of the range reference that cell?

so next month, I just need to change the one cell to N1 and all the data changes?

 

[SkipVought]

Please post a sample of your data that is representative of your problem.

Also check out Excel HELP on the OFFSET function.

It could be that changing the row HEIGHT value (in N1) could adjust ALL your dynamic ranges, if the HEIGHT referenced N1.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[xlhelp]

The whole idea behind a dynamic chart is have it expand as new data is put in. Both Jon and Tushar's examples let you do that. I guess you have to try it before you start questioning the validity.

Bernard Lingme also has a good example of Dynamic charting [

http://people.stfx.ca/bliengme/ExcelTips/Dynamic.htm

]. I haven't looked closely, but probably all of them are similar. I use Bernard's example in my work.

A man has only two choices: He can be right or he can be happy.

 

[Topcat04]

Xlhelp

Thanks for your help and advice, I really appreciate it.

I think I must be missing something though as each reference required the full data range to be typed in. so with the 25 charts, they each have a common dateline axis, so that can be done once with the dynamic solution. However because the plot series are on different lines, eg line 1, line 4, line 7, line 10 etc, it appears to me that I would need still do 25 ranges to be able to do it with Dynamic.

I was hoping that there was an indirect function or somethign similar that could change the M to N, so as all the charts are in place, I could change the data series to the new formula this month and then next month, I would only have to change the one cell that currently had M in it to N.

Maybe what I am describing is just not possible and the only solution is to make the changes to named ranges 25 times each month.

Skip, not sure how to post a sample - never done that on here before, if you can help me I will certainly try.

Thanks,

 

[SkipVought]

xl,

I'm guessing that the OP has a table with EXTRA ROWS that they do not want charted, until that month is to become active.

Topcat,

That, in itself, is not a best and accepted practice. If you use the Data > List... feature, when your add the date for the next month to your table, then all the forumlas in the table will propogate in that row. Your table should be only as large as the data you are plotting.

Conversly, you could AutoFilter the date to display rows whenre the date is <= some date. Your chart can be configured to display only visible rows of data.

I would personally opt fot the former List feature solution.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[xlhelp]

I will bow out of this. Thanks.

A man has only two choices: He can be right or he can be happy.