• 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 with Option and Check Button

S

shab

New Member
Hello All,


Here I am again with my Excel Learning Questions. So far this forum is helping me a lot to learn new Excel stuff and I must thank Hui and NARAYANK991. I tested the follower as per given instructions from http://chandoo.org/wp/2010/08/31/dynamic-chart-with-check-boxes/


and it worked fine. However, this time I am trying to use above on my own spreadsheet and seems a little tricky this time.


Please see attached for reference:

http://www.2shared.com/file/7EXx36MT/ChartwithOptionsButtons.html


Problem:

I have "All-in-One" sheet containing some grouped data such as OSBL, Utilities, Common, etc. which has Planned, Planned-A (Planned Accumulated) etc


I want to have an option(radio) button for each grouped data such as OSBL, Utilities and then have a chart with Check-Boxes to plot only selected such as Planned, Planned-A, Forcast, Forcast-A. For For Accumulated items I want Line graph and for Planned, Forcast, Actual I want to plot Bar Chart.


One thing which I am trying to have: the ability to display or hide data and only show chart mean like we have in http://chandoo.org/wp/2010/08/31/dynamic-chart-with-check-boxes/


Any help/advice/link to read will be appreciated. I am not sure what other options I have to work on the data I have. Please note I am using Excel 2003 and not very much familiar how to represent my data into better chart.


.SHAB
 
Shab

You will need a series on the chart for each set of data

The series will extract the data from the source

The series range will have a formula like

=if(option button 1 link cell <> true, na(), get data from source)

This way when the option button isn't selected the series range will show #n/a which will result in the series not being plotted

I'm not near a PC so can't give you specifics for your file
 
Hello,


I think I am lost, I can not figure out how to bring the data from "All-in-One" Sheet when selecting an option/radio and then have a check box for each item such as planned, planned-A etc.


Please see attach and help me figure out that:


http://www.2shared.com/file/Yaf94oWT/ChartwithOptionsButtons.html


SHAB.
 
Shab


Have a look at the mod I made to your file here: https://www.dropbox.com/s/yo0af05h1ly7rtn/ChartwithOptionsButtons-1_Hui.xlsx


I assumed the blocks of data on the Allinone page were in the same order as the Option buttons along the top as they have no titles
 
OMG!Wow! Hui that is it!Now I know the new way to play with data in Chart! However, few questions to ask you to understand what you did better:


1. In "=IF($A5,OFFSET('All-in-One'!C$4,($A$2-1)*8+2,),NA())" What is "($A$2-1)*8+2" doing?

2. In "=IF($A5,OFFSET('All-in-One'!C$4,($A$2-1)*8+2,),NA())" What can go after "," in "($A$2-1)*8+2,)" because IF - THEN - ELSE has NA() right?

3. Can we put some meaningful/customize word(s) instead of #N/A

4. To make the Chart more Dynamic, Can we add Check Boxes for C3:S3 Titles as well? which means playing with data horizontally and vertically on the same Chart at the same time? Just in case we would like to display the Chart for only Year, Quarters, Months, or combination of those


SHAB
 
Shab


1. ($A$2-1)*8+2 is working out the row offset value for your selected data. Follow the values through and see what it does


2. ,) is the same as ,0) meaning there is no Column Offset Excel doesn't need a 0, but you can put one there if you want


3. You can't put something else instead of #N/A because that is required to turn off the plotting in the chart

Of course you can repeat the table elsewhere

Then use the elsewhere table for the charts source and Change the formulas in the Here table to have something else, like "some other text" or 0 etc


4. So you want to have the "OSBL","Utilities","Common","Utilities plus Common","OBSL plus Common", "All in one" cumulative in 1 chart ? I Don't think that is wise as your data already included accumulations and a Total item already
 
Thanks for the explanation, Hui.


For the item#4, actually for each category I have Quarters an Yearly I wanted to turn off monthly from Jan-Dec so that only quarterly and yearly data can be shown for each category.


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