• 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.

Dynamic Chart

G

GB

Member
Hi,

I need some guidance in best practice to achieve a dynamic scatter chart. I have read many blogs, tried a few things, got confused, now calling for help. I tried using a chart from a pivot table but I want to stay clear from that option as I found it too restrictive in some ways. I am using excel 2010.


Situation...

In the following attachment I have 5 columns of data. Each column has its own named range, using the index formula as I see this being the most efficient as my actual data size is much larger and dynamic in length.


What I need help with...

On a separate worksheet (away from my data source) I want to create 2 drop down selection boxes so I can filter my data to plot a chart. For example I want to select from named range "WN" the values 7,8,9 and from the "DN" named range select B,C. My first problem is I want the drop down selection to show unique values, not every value as you will see my result and to be able to select multiple values from each list, not just one from each.


My second problem is getting this selected data to a chart and behaving dynamically when my selection changes. When the data is selected (by using a drop down box of some kind) I want to plot my named ranges "LT" on the X-axis and "PID" on the y-axis. In addition I want to plot "VP" on a secondary y-axis.


The chart based on this data selection would plot 6 lines for 7B, 7C, 8B, 8C, 9B, 9C and 6 lines for the "VP" output.


http://dl.dropbox.com/u/60464004/Excel/Dynamic%20Chart.xlsx


Thanks in advance.

GB
 
Hi GB ,


I have not gone through your workbook in detail ; I was looking at your dynamic ranges , and I think the following correction should be applied to all of your dynamic ranges :


For WN :


=Data!$A$2:INDEX(Data!$A:$A,COUNTA(Data!$A:$A))


A more general tip : to know whether you have defined your named range correctly , click on the named range in the Name Manager , place your cursor in the refers to box , and see whether the actual range in the worksheet is selected ; if no , then probably your definition is not correct.


Can you make the above changes to all the other named ranges in your workbook and see if there are any improvements ?


Narayan
 
Hi Narayan,

thanks for tip, I have cleaned up the named ranges, but still have all the issues as described in my first post. Here is my new version.


http://dl.dropbox.com/u/60464004/Excel/Dynamic%20Chartv1.xlsx


cheers

GB
 
Hi Birko ,


Now that your named ranges have been cleaned up , can you refer to the following question and the formula given there , to derive a unique list of data from a list with duplicates ? This unique list can then be used in your drop-downs. Making the chart will be the step after this.


http://chandoo.org/forums/topic/how-to-create-unique-list-based-on-data-available-in-range-of-cells


Narayan
 
Hi Birko ,


The drop-downs have been done in the workbook here :


https://docs.google.com/open?id=0B0KMpuzr3MTVX1ZaOVhSQUJWMkk


Narayan
 
Hi Narayan,

that question/answer came in good time. I will check it out tonight and come back to you.

regards

GB
 
Hi Narayan,

I appreciate your feedback so far. These formulas are new to me and hard to absorb but I am working through these slowly. The problem with the drop down lists is they won't let me select multiple values as original required. I am heading down the wrong path here or do you have a way of making this happen?


cheers

GB
 
Hi Birko ,


I think it is not possible to have multiple selections from a data validation drop-down list. If you really want this feature , it will have to be done using a listbox and VBA.


Narayan
 
Thanks,

I have hit the brick wall now. I was hoping for a non VBA solution. I will keep investigating unless you have any further ideas to solve this.


regards

GB
 
Hi Birko ,


I think what you want to be done can be done in other ways , without having to go through the multi-select route. Can you specify clearly what you want done ? Will it always be 6 charts on the primary axis and 6 on the secondary ?


Narayan
 
Hi Narayan,

typically 6 on the primary and 6 on the secondary is what I want to see, but no limited to these values. My max will be 8 and min will be 4. I was hoping my 1st post was clear enough on what I was trying to achieve. Which part do you need further information on?

regards

GB
 
Hi Birko ,


Another way to have multi-selection is using checkboxes ; can you say how many options will be possible in columns A ( WN ) and B ( DN ) ? If they are not too many , you can have checkboxes for each of the options , and depending on which checkbox is checked , that series can be added to the chart.


Narayan
 
Hi Narayan,

can you provide an example so I can build on what you show me as I haven't used these before.

cheers

GB
 
Hi Birko ,


Can you check out the following link ?


http://peltiertech.com/WordPress/interactive-charts-with-checkboxes-and-formulas/


Narayan
 
You must log in or register to reply here.
Back
Top