• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Named Data range changes to Cell reference [SOLVED]

W

WouterJ

New Member
Hi there,


Today I tried to make a dynamic chart using named ranges.

In the file in the link I've made a very basic P&L statement with a chart. The point is to select a certain month and the graph will show you the several lines per month.


Everythings seemed to work fine, untill I noticed that whenever I selected May (last month) and then selected a previous month, the graph on the left doesn't change back to the previous month, but will show the figures until May. After some frustrations and searching, I found out that the data range in the chart changes from a named range into a cell reference and therefore the graph isn't as dynamic as I like it to be...


Any suggestions why this is happening and how to correct this (I would prefer a correction and not a workaround, because I think that this will compromise further development)


https://dl.dropboxusercontent.com/u/94516207/Charts%20tryout.xlsx


cheers,


Wouter
 
Hi, WouterJ!


In the 2nd chart (left bottom) use named ranges for data series as well as you used in 1st chart (top right), e.g. for the TO values:

='Charts tryout.xlsx'!TOpermonth

instead of:

=Sheet1!$B$16:$F$16


But you'll have to define first those dynamic named ranges for rows 4:9 as well as you did for rows 16:21.


Regards!
 
Hi SirJB7,


Thanks for your reply.


The data range is initialy defined as:

='Charts tryout.xlsx'!TOpermonth


but once you select May, this changes to :

=Sheet1!$B$16:$F$16


And I'm not sure why I should name the dat in rows 4:9 aswell?


cheers,


Wouter
 
Hi, WouterJ!


At a first glance I understood that you wanted to make charts for the 2nd table. Try changing series names for 2nd chart from:

=Sheet1!$A$16 (to 21)

to:

=Sheet1!$A$4 (to 9)

as in 1st chart.


It works, but don't ask me why! :)


Regards!


EDITED


PS: I assume that once you've selected the last month, May, the series name range plus the data ranges match with the whole 2nd table (PENL3), since then the entire table is assumed and it never redefines the chart data series ranges.
 
Hi Wouter ,


If I have understood your problem correctly , it is that when you select May from the drop-down , the chart displays all the 5 months of data ; now when you select , let us say March from the drop-down , instead of displaying 3 months of data , it continues to display all 5 months of data.


The problem is a strange one , and I think it is related to the fact that selecting May makes the entire table get selected.


What I did was delete all the series in the table , and re-insert all of them ; after that the chart behaved properly ; if you want I can upload the workbook.


Another thing you can try is to insert a blank row at the bottom of the table , and see if the chart still behaves the way it behaved.


The chart should also behave properly , if you don't include NET MAR ( which is the last row of the table ) in the chart.


Narayan
 
Bizarre!


Converting the lower Table back to a Normal Range also fixes the issue
 
Thanks for your replies!


The second solution from Narayank seems to work. I've extended the second table with one row and the graph is working properly now!


Thanks again :)


Wouter


PS. How can I change the Title of the thread? I want to add [solved] to it. Or is it a mod thing?
 
You must log in or register to reply here.
Back
Top