Hi Guru's,
I'm using Mynda post recently (Slicer Controlled Interactive Excel Charts) for my presentation and let me explain my purpose of using this;
We had 5 difference manufacturing lines and each line is producing different product so I would wanted to used this ideal to present for the past 7th months what is production output VS the HC (head count) required for these 5 manufacturing lines so I used this ideal;
Slicer will show the 5 Manufacturing lines as selection so when user select the "Mfg1" it will show Manufacturing line 1 data (Output = Prod + Eval as stack chart) VS HC (secondary axis for line chart) for the past 7th months. So with this, I've created the dynamic data range for below;
1. ProdRng = Production dynamic range
2. EvalRng = Evaluation dynamic range
3. HCRng = Head Count dynamic range
4. MonthRng = Month dynamic range
5. ProdSeries = Manufacturing line dynamic range for lookup purpose when user select on the slicer, it will look up for the Prod, Eval and HC for plotting the chart.
I separate my data and chart in 2 different tab sheet (Sheet1 hold the data and sheet2 hold the chart) and I've tested the dynamic range and is working but interestingly when I input this dynamic range into the chart, it prompt me an error (I've print screen and save it under the "Error" tabsheet in my attach file so I'm at lost in this junction and would like to seek some advice from the gurus.
Thanks you for your time by reading my post and appreciate your help in advance.
Hi Patrick,
I'm pleased to see you found the Slicer filter technique useful.
I think your use of INDIRECT in your dynamic formula is causing the problem. You can significantly simplify the formula you use for the dynamic named range by adding a row to your source data in Sheet1 for the Product (see row 2).
See example attached.
Mynda
I should have also commended you on your clear and concise explanation included in the Excel file. Simply perfect. Thank you for making it easy to understand the problem.
Hi Mynda,
One work "WOW"... I didn't know it can be done that way, it's simple and nice. This show that I still have a long way on this topic (dynamic range) and I would like to thank you for your time in guiding me and really appreciate.
I'm glad that I could show the problem that I face to guru(s) like you and understand my problem.
Once again, thanks you very much.
Best Regards,
Patrick
Hi Mynda and Guru(s),
I'm continue with this topic as I'm stuck with the pie chart dynamic range, would anyone give me a guidance on how to write the dynamic range for Pie chart data source?
I've attach the file and under the "chart" tabsheet are the pie chart that I wanted to show in term of MTD data for Prod and Eval by Mfg in term of percentage.
Once again, thanks you for your time in guiding me.
Best Regards,
Patrick
Hi Patrick
Normally I would looking at what is selected in the 1st row of the PivotTable and then do a lookup using VLOOKUP/INDEX/MATCH/OFFSET etc. to get the required values.
I have attached an example of how I would do it in your case.
Hey Sunny,
Another great solution and appreciate your help and your time.
Have a good weekend.
Regards,
Patrick
