Register
Log In
Unanswered
Home
January 16, 2014
Offline
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.
See Answer
July 16, 2010
Online
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
Answers Post
July 16, 2010
Online
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.
January 16, 2014
Offline
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
January 16, 2014
Offline
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
Dashboards
June 25, 2016
Offline
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.
Most Users Ever Online: 57
Currently Online: Mynda Treacy
18 Guest(s)
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 651
Frans Visser: 210
David_Ng: 96
mey tithveasna: 71
A.Maurizio: 60
rathanak: 58
yhooithin05: 54
Anders Sehlstedt: 47
julian: 46
PaulFogel: 37
Newest Members:
sugi gunawan
janja mesec
John DiMartino
lizette acosta
eric johnson
Kim Bond
Nichelle Stevens
Manuel Rodriguez
peter wang
Amit Kanekar
Forum Stats:
Groups: 2
Forums: 18
Topics: 935
Posts: 4405
Member Stats:
Guest Posters: 1
Members: 42350
Moderators: 1
Admins: 3
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea
Moderators: Genevieve Tupas
