In the chart data source named ranges are replaced by references

[Harry Covair]

Hi All,

In an Excel chart, I want to display the data two months before and one month after an selected day.

To do so, I defined some named ranges as

Select_years:

'OFFSET(Sheet1!Start;0;MATCH(Sheet1!start_date;Sheet1!years;0)-1;1;MATCH(Sheet1!start_date;Sheet1!years;0)+1)'

Data_product:

'OFFSET(Sheet1!Start;5;MATCH(Sheet1!start_date;Sheet1!année;0)-1;5;MATCH(Sheet1!start_date;Sheet1!année;0)+1)'

years: Sheet1!$R$1:$AC$1

product: Sheet1!$Q$6:$Q$10

start: $Q$1

start_date: $Q$23

In the « chart data source » dialog box, the following instruction is manually written in the « chart data range »

'=Sheet1!start;Sheet1!select_years;Sheet1!product;Sheet1!data_product'


The chart is perfectly designed and represents well the desired data

Problem

When I re-open the « chart data source » dialog, the named ranges are replaced by references which make the chart static and no more dynamic.

'=Sheet1!$Q$1;Sheet1!$S$1:$V$1;Sheet1!$Q$6:$Q$10;Sheet1!$S$6:$V$10'


Could some people assist me to explain this phenomenon and why not suggest an solution.

Thanks in advance

Regards

 

[shailyog]

That's how Excel does it. the Chart is still dynamic. did you try to change the criteria to see the result?? I have used same procedure and my Chart works pretty fine although it shows static reference in data source.

 

[Harry Covair]

Hi shailyog,

Thanks for the answer.

Yes, I did and the chart remains unchanged.

But if I re-write manually the content in the "chart data range", the chart is well updated.

That 's what I call a manually dynamic chart.

Regards

Harry

 

[shailyog]

can you please upload your file with dummy data. Here is how to do it


http://chandoo.org/forums/topic/posting-a-sample-workbook

 

[Harry Covair]

sorry for the waiting time of the response but I did not used to this kind of manipulation

I created a Dropbox account and here is the link

https://dl.dropbox.com/u/94100164/Sample%20file%20dynamic%20chart.xlsx

Regards

Harry

 

[shailyog]

I have created few new name ranges and a new chart.


see if this is the result you want:


https://www.box.com/s/767c1a38192024023941

 

[Harry Covair]

Great!

Thanks for the solution.

The chart is working fine when changing the start date.

The suggested solution was thus to create a named range for each series.


Best regards

Harry

Is there a procedure to declare the post as solved?

 

[SirJB7]

Hi, Harry Covair!


Better later than never welcome to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here.


As a starting point I'd recommend you to read the three first green sticky topics at this forums main page. There you'll find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on).


Among them you're prompted to perform searches within this site before posting, because maybe your question had been answered yet.


Feel free to play with different keywords so as to be led thru a wide variety of articles and posts, and if you don't find anything that solves your problem or guides you towards a solution, you'll always be welcome back here. Tell us what you've done, consider uploading a sample file as recommended, and somebody surely will read your post and help you.


And about your question...


There's no such a procedure, topics are always open. Usually is highly appreciated if the user who started it does exactly what you did: give feedback and tell us how if succeeded or not.


So glad you solved it, thanks for your feedback and for your kind words too. Welcome back whenever needed or wanted.


Regards!