• 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 - Pivot Table

RobSA

RobSA

Member
Hi Guys,


I am working on a rather complex dashboard and require some assitance.


I the dynamic charts posted on the Chandoo.org site there is a fille offered for downloading called ChartDate Range2007.xlsx.


This is the closest I can get to the desired effects I require.


My objective is to use this file so that the chart / graph will have all the dates showing on the bottom axis. In my case I have changed the dates to months and would like the full 12 months to show on the bottom axis, despite the selection made, say August.


The view would then show the 12 months but only the data for the 8 selectd.


I look forward to some ideas and replies


Regards

RObSA
 
Is this the workbook you are referring to?

http://cid-b663e096d6c08c74.skydrive.live.com/self.aspx/Public/dynamic-chart-ranges-tutorial.xlsx


Link was found on this page:

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


Please find my link attached https://www.dropbox.com/sh/fxha3cu3y2llvm9/xkE8eRGxz1


If you open the Quality 6 file the dash has a worksheet called Project Report with Audit results reflecting in the top left corner.


I would like to get activity shown in the second file to work in the Quality file.


I hope that makes sense.


Regards

Rob SA
 
Hi Rob ,


Can you check out the file here ?


http://speedy.sh/A2SAW/Quality-Dashboard-6-r1.xlsx


I have defined a dynamic range called Audit_Series , which is then used in the Audit chart. The dynamic range takes into account the project selected , and the month selected ; the year selected is not being used ; in case you want to include this also in the dynamic range , you will have to modify the formula for the range accordingly.


Narayan
 
Hi Narayank,


Thanks so much for your inputs and help.


I am not a specialist in pivot tables or dynamic chart and would appreciate a little more of you time.


Please would you explain the following code and how it works ?


=OFFSET(Project_Names,MATCH('PROJECT REPORT'!$O$2,Project_Names,0)-1,4,MONTH('PROJECT REPORT'!$AA$2:$AE$2&0),1)


Then secondly the idea I have in mind (if at all possible) is to have the axis values (J,F,M,A...) to all show however only the data in the graph show for the specific month and behind.


If I can try explain if I select the project (say Brits Hospital) , the month (March) and the year (2012) then all the months will show on the chart but only the data up until March of that year, for that specific project will show.


Thanks again for your help and i am sure that once I know how this formula works things will go better.


Regards

RobSA
 
Hi Rob ,


What you have explained is exactly what the formula is doing , except that the year has not been included.


Let me explain how we would go about doing it manually ; suppose the user has selected the following options :


1. ADCOCK INGRAM

2. SEPTEMBER


We would look in column B on the tab 2011 , and go down till we came to ADCOCK INGRAM ; then , since the values are in column F , we would move to the right by 4 columns ( C , D , E and F ) ; now we would include as many rows as the number of months that have been selected e.g. since the user selected SEPTEMBER , we would include the 9 rows starting from row 35 ; if the user selected MAY , then we would include only 5 rows starting from row 35.


I assume that since you have labelled the tab 2011 , the values for the other years will be in similarly labelled tabs. Including the year also will involve selecting the right tab , which will involve using the INDIRECT function. Let me know if you have the data for 2012 ; we can take it from there.


Narayan
 
Hi Narayan,


Thank you very much aagain for your help.


I have included a dummy worksheet for 2012 as requested.


https://www.dropbox.com/sh/fxha3cu3y2llvm9/xkE8eRGxz1?m


The first revision is giving up a few challenhes in that it is not showing dynamically on my side. I use Excel 2007 at home and develop most of this on 2007. The office is using 2010.


I can see that the change you made does have an effect as the axis shoing is only up until September. I do however want to show the entire year, January to December, but only show the data up until September, if I select September.


I hope this doe snot cuase to much of a bother on your side.


Thanks again


RobSA
 
Hi Rob ,


Sorry , but I may not be able to give more time today to your file ; if you can wait till tomorrow , I can continue tomorrow morning. Otherwise , I hope others can step in and help out.


Narayan
 
Hi Narayan,


Thanks for the reply and your time.


I can wait - no problem and wil also see if other wish to contribute.


Regards

RobSA
 
Hi Rob ,


Can you check this file ?


http://speedy.sh/u4WSZ/Quality-Dashboard-6-r3.xlsx


A few named ranges have been created :

[pre]
Code: 
Audit_Series

=OFFSET(INDIRECT("Project_Names_"&TEXT(Selected_Year,"0")),MATCH(Selected_Project,INDIRECT("Project_Names_"&TEXT(Selected_Year,"0")),0)-1,4,MONTH(Selected_Month&0),1)

Project_Names_2011       ='2011'!$B$7:$B$332
Project_Names_2012       ='2012'!$B$7:$B$332

Selected_Month           ='PROJECT REPORT'!$AA$2
Selected_Project         ='PROJECT REPORT'!$O$2
Selected_Year            ='PROJECT REPORT'!$AF$2
[/pre]
Narayan
 
Hi Narayan,


Thanks for your help.


I have tried both files but they do not seem to work on either 2007 or 2010 versions of excel.


Regards
 
Hi Rob ,


I found that the chart series is no longer what it was in an earlier file !


It has been changed to =[5]!Audit_Series


Change this to ='Project Report'!Audit_Series , and it should work.


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