Plotting a chart problem
Hi Guys,
I'm hoping someone out there can help!
I have a problem with a chart that I just cannot get my head around.
Please see the attached and it may make more sense.
I have a sheet with some extracted data on for various sites.
What i need is only ONE chart that shows me the grade score for a date range (as a column chart, line or scatter) for just one site at a time but have a drop down or something similar to choose another site form and then it shows there data.
As above, i only want one chart and be able to change the site and it shows just that sites info on grade score.
I also need 3 trend lines (or simliar) to show the upper, lower and preferred limits.
Any help on this whould be awesome as i am struggling.
I've tried pivots but i can't seem to get it working properly.........
Cheers Ears
See attached pivot table and chart
Hi Andy,
Thats great but the spreadsheet i attached was just a little snippet of the data.
When i try and do it using the pivot table method on the actual sheet its just goes to pot!!
I have attached the actual data i'm using and you may see where i'm going wrong!
When i try and recreate your pivot it keeps putting the sum across the bottom of the chart for each series, upper, lower mean and comp and the chart doesn't look right.
I need all of them on the chart (Comp, Upper, Lower and Mean) but the last three as a trendline or just a line.
What am i doing wrong?
Cheers, Turbofatty
On the pivot chart drag the data fields on the x axis to the legend on the right.
Cheers Andy!
I couldn't get it to work at first but then twigged!
I would have been there for years!!
Much obliged!
One more thing!
When i format the chart (colours etc) and then change the site in the drop down the format disappears!
How do i keep the formats on there all the time??
Cheers, Turbodatty
It's a known problem
http://www.excelforum.com/excel-char...ormatting.html
****.................
****...........
Last edited by Andy Pope; 02-11-2010 at 11:06 AM.
What's the problem? You need half a dozen lines of code to format as desired.
I've recorded a little macro to do it a just created a button to run it from.
The sheet isn't for my use so I need to make it as simple as possible for the user.
Cheers for all your help, i'll no doubt be back soon with another one.........
No need for any buttons.
Just but the formatting code in the sheets PivotTableUpdate event. Right click sheet tab and pick View Code.
Code:Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable) With ActiveSheet.ChartObjects(1).Chart .SeriesCollection(1).Border.ColorIndex = 3 .SeriesCollection(2).Border.ColorIndex = 10 .SeriesCollection(3).Border.ColorIndex = 43 End With End Sub
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks