Dashboard - Chart not taking Total and Average Values and Picture link

[Thomas Kuriakose]

Respected Sirs,

I adapted a dashboard created by Asheesh Sir to suit our requirements

I am clipping the file that I created using this dashboard. I am facing two issues currently=

1. Initially the on clicking the Overall branch the picture picked up was AAA, subsequently I created a picture of Overall and now Overall is appearing when I select the overall, but I am not able to understand the linkage with respect to the name manager and picture, when I give the address of one picture, but on selection it changes and now the relation between name manager and picture is not correct.

2. We need data for months in 2015 and 2016, but the RngTotal, RngAverage works only for two months in the Chart values of Total and Average.

Secondly the branch wise chart works for selection of the three branches and not the overall, How to get the chart working when overall is selected.

Kindly check and let me know how to get this working to suit our requirement.

Thank you so much,

with regards,
Thomas

 

[Luke M]

Hi Thomas,

Let's look at how the picture selection was supposed to work. If you clicked on the shape on the dashboard, you'll see that it's linked to:
=Pic
So, let's open up the Name Manager.



Pic is using the CHOOSE function. Here, we list at all the different named ranges for our shape, and the control cell to determine where to choose. Note that in our list, Overall range was listed last (item 4).
Here's where your problem came up. If you look at the definition for the 'Overall' range, it's defined as AI4:AJ8. But, the shape that's there in the Calc sheet is labeled CCC. So, we have a mix-up in what our named ranges are and the labels. First, let's change the text in the shapes to match the Named Ranges. Then, since you're wanting the Overall option to be displayed first, we'll change the formula for 'Pic' to this:
=CHOOSE(calc!$U$4,Overall,AAA,BBB,CCC)

Now our named ranges match the shape text, and we've got things back in sync.

For point 2, I changed the formulas in the calc table in range O1:Q10. Please look at them. For the Total, change the YEAR comparison to be something like:
Data>=(Year(today)-1)
and average then becomes
Total/(MonthsThisYear + 12)

For the last point, I changed the formula in cells B7 and across to a simple INDEX function.

Hope that helps Thomas!

 

[Thomas Kuriakose]

Respected Sir,

This is amazing.

Thank you so much for the detailed explanation and for the solution provided.

Thanks once again,

with regards,
thomas

 

[Thomas Kuriakose]

Respected Sir,

On observation, the 6m trend chart has #n/a results on selection of Overall.

Kindly check this,

thank you so much,

with regards,
thomas

 

[Asheesh]

Hi,

In Cell B6 of "Calc" tab, put the below formula and drag it to the right..

IF(B7<>"",INDEX($A$9:$M$28,MATCH(IF($V$4="<>","Overall",$V$4),$A$9:$A$28,0),)/MONTH(MAX(myTbl[Month])),AVERAGE(B9:B28))

 

[Luke M]

Thanks Asheesh, I completely missed that one.

 

[Thomas Kuriakose]

Respected Sir,

Thank you very much for making this work and for all the inputs provided.

Thanks once again,

with regards,
thomas