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

Conditionally changing a chart

N

nicktieri

New Member
Hello,


I am trying to conditionally change a chart based on a pull-down menu. I have been following the guide found here with mixed results:


http://chandoo.org/wp/2008/11/05/select-show-one-chart-from-many/


I am using excel 2010. I have followed the steps outlined and it mostly works but I have run into some really strange results. Sometimes (but not always) the picture shows more cells than what is defined in my INDIRECT statement. Here is the statement I am using (I've defined the name as getCht2):


=IF(Report!$AE$9="Sales",INDIRECT("'Charts'!$c$2"),IF(Report!$AE$9="Cancel",INDIRECT("'Charts'!$c$3"), IF(Report!$AE$9="Fulfillment",INDIRECT("'Charts'!$c$4"),IF(Report!$AE$9="activation", INDIRECT("'Charts'!$c$5"), IF(Report!$AE$9="support",INDIRECT("'Charts'!$c$6"),IF(Report!$AE$9="CSAT",INDIRECT("'Charts'!$c$7"), IF(Report!$AE$9="Cost",INDIRECT("'Charts'!$c$8"),IF(Report!$AE$9="usage", INDIRECT("'Charts'!$c$9")))))))))


This works for some entries, but not others. When I select Sales, the picture compresses cells c2:e2 into the frame. When I chose Cancel, the picture compresses c3:d3. All others work fine.


I am doing this for 3 different pictures and 1 works perfectly, while the other two show errors like this. I've triple checked the formulas, and cannot find the problem. Is this a 2010 issue? Is there a better way to accomplish this goal (conditionally showing a graph based on a drop-down menu) in 2010?


Any help would be greatly appreciated!

Nick...
 
You can reduce the length of your formula by using a different attack probably. Let's say you have a list of all the chart names in A2:A9. Your formula could then be:

=INDEX('Charts'!$C$2:$C$9,MATCH(Report!$AE$9,$A$2:$A$9,0))
 
Thanks Luke. That definitely simplifies the formula, but unfortunately it doesn't fix the problem. I am showing the exact same results (it is referring to the range 'charts!'c2:'charts!'e2 when I select "sales" instead of just 'charts!'c2). Really strange.
 
None of your charts are "leaking" over into extra cells by chance? What's even stranger, the INDEX formula should not even be capable of returning a cell outside the 1st arguments definition. I'm afraid that I don't have much more help beyond that. =(
 
No leakage. But I think I fixed the problem. I copied all of the charts to rows f and g and referenced those rows and suddenly it works. No idea why but I won't complain. Thanks for your help! Now if I could only get the conditional formatting to work consistently.
 
This is a long shot, but perhaps corruption has crept in? You could try closing the file, then go to open. Select the file in the dialogue, and use the dropdown on the "open" button to select "open and repair". It doesn't happen too often, but occassionally, it helps.
 
You must log in or register to reply here.
Back
Top