Applying Chandoo s interactive dashboard to more than one series

[Prinz Jaymes]

Hi Guys,
I love coming here!
Been trying to create an interactive dashboard but with more than one series in Vba.

Excel Experts and Ninjas, please see attachment to understand.

Basically, how can i get the interactive chart to flow from one category or table to another?

 

[Luke M]

Hi Prinz Jaymes,

I took at crack at figuring this out. I think you were wanting the data in the first block to change, based on which cell you hovered over on Dashboard? See the attached to see if that work.s

I'll admit, I also went through the VBE and cleaned things up a little. Hope you don't mind.

 

[Prinz Jaymes]

Hi Luke
Anyway to prevent the zeroes from showing up on Chart?

 

[NARAYANK991]

Hi ,

I think the dashboard needs some correction , so that the selection of the project changes the hover highlight.

See this file , which is Luke's with some minor changes.

Narayan

 

[Prinz Jaymes]

Hi Narayan
I tweaked the data to include the missing area you mentioned.

Please see attachment.

By the way, anyway to format or set some rule such that cells from revolving series (with zero) below last TEXT/NAME on far left of table become blank ?

That way, they do not feature in chart.

 

[NARAYANK991]

Hi Jaymes ,

I am not sure of your requirement , but can you see this file and comment ?

Narayan

 

[Prinz Jaymes]

Narayan,
That was very close.

Documents 563 #N/A #N/A #N/A
Pages 8340 #N/A #N/A #N/A
0 #N/A #N/A #N/A #N/A
0 #N/A #N/A #N/A #N/A
0 #N/A #N/A #N/A #N/A


The rule you set applies to the Entire series, if I am not mistaken and it excludes all zeroes.

Basically, Pages above has values from Left to right(including zeroes). I want to include zeroes since the information relates to pages.

However, I don't want any zeroes on that first column. They should be #NA if they are not TEXT/NAME
If there is one, then all values across and below should be #N/A.
Hope that helps.

see attachment

 

[ushaanu]

hi , all ,

file is very good , dear all i have some data i try to convert this file to aacording to my data but it`s not working can you help on this

i file my data in weekliy data

in my case i need data branch wise if i select branch shows that branch data in list og projects.

thanks
anu

 

[NARAYANK991]

Hi Anu ,

I am not able to make head or tail of your requirement.

You say your data is on the tab named Weekly Data , but how does the chart which you want on the Dashboard tab relate to this data ?

I suggest you create one full fledged chart , with just one series , with all of the proper formatting for the axes , titles , legends ,... ; this need not be a bar chart , it can be any style of chart.

With reference to this chart , you explain how you want to add interactivity to the chart , so that as the user changes some selection , the chart should change accordingly.

If the above is clear , then it will be an easy matter to reproduce your chart on the Dashboard tab and add the desired interactivity to it.

Narayan

 

[ushaanu]

hi Narayan,

thanks for reply I have data like enclosed file with chart is this possible my data
covert this dash bord . if it`s is possible it is grate for me.

in my data no need to calculation req I think.

thanks
Anu

 

[NARAYANK991]

Hi Anu ,

I am still in the dark , but now for a different reason ; the file you have uploaded has 8 sets of data and 9 charts.

Which set of data and which chart do you wish to make interactive , and what will be the changes the user will make , which need to be reflected in the corresponding chart ?

Narayan

 

[ushaanu]

Hi Narayan

Good Morning!

Thank Once Again , actually I have 4 Branches in my data it`s only one branch data I need when I select one branch then respective branch reports data and chart reflect in dash Board .likes Yours
In Enclosed Sheet I Give One exp like I select Neena Branch and report head count Report

Thanks
Anu

 

[NARAYANK991]

Hi Anu ,

Can you see the attached file ? I have done only the first 3 charts.

Narayan

 

[ushaanu]

Hi Narayan

Thanks It`s Good

One Thing I want know how to link chart with Report Head

and 2nd thing I need when select Report Head in below of chart the excel number date also show like Your " mega report " File and in your mega file when we select project drop down box show the selected Report.

and Is it Possible If I have two Branch with same Report heads . can we 1st select branch then report head and dash board shows the data and chart for respective Branch Data.

Thanks
Anu

 

[NARAYANK991]

Hi Anu ,

Now the drop-down is linked to the hyperlink.

Your other issues I am not able to understand.

Narayan

 

[ushaanu]

hi Narayan,

thank you ,
my 2nd issue is if I select Count report then count table screen shot shows below like enclosed sheet .if I select FINANCIAL REPORT , FINANCIAL REPORT Table screen shot like your mega report

and can you tell how to like other chart option so that I can link all report heads

thanks
anu

 

[NARAYANK991]

Hi Anu ,

That is because the pie-chart pertaining to the Count report is not a pie-chart but an image of a pie-chart ; the image and the other charts have been developed by you , not by me.

If you replace the image by a pie-chart in that range of cells , then when you select Count , you will see a chart on the Dashboard.

To bring in the other charts on to the Dashboard , just extend the CHOOSE formula in the named range definition to include all the other charts.

Narayan

 

[ushaanu]

hi Narayan,

Can you tell me how can I change my file so that it` reflect like your file.

thanks

Anu

 

[NARAYANK991]

Hi Anu ,

It takes longer to explain something than to do it !

I have modified the named ranges to take into account the change in the Branch Report Head ; look at the change , and replicate the same for the remaining 6 named ranges.

I have pasted a list of the relevant named ranges on an added sheet Sheet2.

Narayan

 

[ushaanu]

thank you!! Narayan,

can you tell me One thing More how do you link Weekly data table snap shot in dash bord when we select project thru drop down box.

Thanks
anu

 

[NARAYANK991]

Hi Anu ,

You can use the same technique that has been used for displaying the charts ; instead of one picture on the dashboard , use two ; the first will display the chart , which it is doing now , while the second can be used to display the data.

You will have to set up as many data ranges as there are charts , and point the picture on the dashboard to each data range , depending on which report has been selected.

Try it ; I am sure you can do it.

Narayan

 

[ushaanu]

Hi Narayan

can you tell me how highlightSeries option works.

actually I put my data in you file now not able to display the chart in dash board.

thanks
anu

 

[NARAYANK991]

Hi Anu ,

The highlightSeries procedure is not working the magic ; it is fact that using a procedure within the HYPERLINK formula allows you to execute the procedure.

The concept was discovered by Jordan Goldmeier , and is explained in his blog here :

http://optionexplicitvba.com/2012/09/17/the-excel-rollover-mini-faq/

Narayan

 

[ushaanu]

hi Narayan,

but in my file where is the mistake why my chart not displaying.

Anu

 

[NARAYANK991]

Hi Anu ,

Give me a day or two ; I'll setup your file and reupload.

Narayan