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

G

gudipudi

New Member
Hi Guys, May i ask any one of you to please help me to generate dynamic chart based on the below condition. ( This is not just any other routine doubt). I tried the wholeday to get to solution.


When I choose Clicks/Impressions from dropdown menu , the graph with various products across last 6 months should change.


As I don't see an option to upload the excel sheet, I request you to please download it from here

www.rama.id.au/dynamic.xlsx


Any help is highly appreciated.


Thanks Guys
 
Gudipudi


Firstly, Welcome to the Chandoo.org forums.


Have a look at: https://www.dropbox.com/s/gdwvumso12o7owd/Dynamic%20Chart.xlsx


Sorry don't have time to explain how it works today
 
Thank you for such a warm welcome Mate and for such a quick response.


May i request you to please help me understand on how you arrived at the solution when you are free some time tomorrow ?
 
Gudipudi


I have added 11 Named Formula to the workbook

The use of Named Formulas allow for the ranges to become "Dynamic"

The named formulas use a very simple formula like:

Code: 
=CHOOSE(Sheet1!$H$1,Sheet1!$B$2:$G$2,Sheet1!$B$16:$G$16)


As the value from the Drop Down in the chart returns the value 1 or 2 to cell H1

The Named Formula can then choose the data from either Row 2 or Row 16 depending on the value of H1

A H1 value of 1 allows Choose to select Row 2

A H1 value of 2 allows Choose to select Row 16


I then changed the Chart Series to look up the Named Formula instead of the range

Select the chart and right click on it, Select Data


Select say the Brand series and Edit

You will see that the Series Values is now set to: ='Dynamic Chart.xlsx'!Brand

etc for the other Series


So you change the Drop Down

Cell H1 is changed to a new value

The Named Formulas all change the references based on H1 &

The chart changes based on the changes in the named formula


You may want to read about other techniques for using Dynamic Formulas in:

http://chandoo.org/wp/2009/10/15/dynamic-chart-data-series/

&

http://chandoo.org/wp/2010/11/04/analysing-large-tables/


ps: I don't recommend using Choose() for any for than 3 or 4 range choices, there are better techniques to handle those
 
den Parteigenossen, die mit religiösen Ueberzeugungen gebrochen hätten,polo pas cher, der Austritt aus der Landeskirche
empfohlen.--
Am 1. Februar 1872 trat Vahlteich seine Festungshaft in Hubertusburg an; später folgte ihm Karl Hirsch.
Mittlerweile wurden aber auch die übrigen Gefängnisse mit verurteilten Sozialdemokraten besetzt. Einzelne
Genossen waren mit sehr harten Gefängnisstrafen bedacht worden.
Der Leipziger Hochverratsprozeß.
Bei der Eröffnungsfeier des ersten deutschen Reichstags am 23. März 1871 im sogenannten Weißen Saale des
königlichen Schlosses zu Berlin trat Fürst Bismarck an den Abgeordneten v. Schwarze heran mit den Worten:
"Nun, Herr Generalstaatsanwalt,chaussures louboutin, was wird denn aus dem Prozeß Bebel und Genossen?" Der Angeredete
zuckte die Achseln und erwiderte: "Gar nichts wird.",christian louboutin pas cher, worauf Bismarck unwillig antwortete: "Dann hätte man
die Leute auch nicht einstecken sollen; jetzt fällt das Odium des Prozesses auf uns." Wenige Augenblicke
nach jenem Vorgang wandte sich der sächsische Finanzminister v. Friesen,christian louboutin, der die Unterhaltung zwischen


Related articles：

 
Hi Mate,


I know its too much to ask, but May i request you to please shed some light on how to add more options in the drop down menu( I understand that i should not expand my options more than 4)


Thanks once again

Rama
 
Right click on the Drop Down, Format Control

Note that it is using Input Range J24:J25

Change the range to suit where you have your selections eg: J24:j27 for 4 selections

Add 2 more Text values to J26 & J27

Change the Named Formulas appropriately to represent your new/extra data
 
You must log in or register to reply here.
Back
Top