Replies

I agree that attribute charts are a more useful way to go. Have a look here for downloadable Excel tools: http://www.freequality.org/beta freequal/fq web site/tools.htm#chart[/url]. There is a cusum tool on this page as well. You could also Google ‘Statistical Process Control’ or SPC for more resources.

Graeme

I agree that attribute charts are a more useful way to go. Have a look here for downloadable Excel tools: http://www.freequality.org/beta freequal/fq web site/tools.htm#chart[/url]. There is a cusum tool on this page as well. You could also Google ‘Statistical Process Control’ or SPC for more resources.

Graeme

The Help on calculated fields/items in Excel is really detailed, alex – not sure I could do any better!

alex,
Right-click on your totals field, select ‘Field Settings…’, Options, select ‘Running Total in..’ from the ‘Show data as’ dropdown, then select Month in the Base Field list. Something close to that should work for you.

graeme

Edited to remove schoolboy error from code!!

Tim,
Pivot charts don’t retain their formatting when you change the data view, or change anything on the pivot table. The Help helpfully suggests you write your own macro to deal with formatting. This is one way, using the Calculate event on the chart sheet; paste the code into the chart’s VBA module:

Private Sub Chart_Calculate()

‘stop screen flicker
Application.Screenupdating = False

On Error Resume Next

‘change the formats to whatever you desire
With ActiveChart
.SeriesCollection(1).AxisGroup = 2
.SeriesCollection(2).ChartType = xlLineMarkers
End With

Application.Screenupdating = True

End Sub

HTH!

Graeme

I use this to change the colour of out-of-limits points on control charts in excel:

Private Sub MarkPoint(ByVal i As Long, j As Long)
””change the marker style of points outside control limits
Dim srs As Series
Dim pnt As Point

On Error Resume Next
Set srs = myChart.SeriesCollection(1)
With srs
Set pnt = .Points(i)
pnt.MarkerBackgroundColorIndex = j
pnt.MarkerForegroundColorIndex = j
pnt.MarkerSize = 7
pnt.MarkerStyle = xlMarkerStyleCircle
End With
On Error GoTo 0

End Sub

A bit of hacking about should get it to work for you – hope it helps

Graeme

I would send the workbook, but it seems to be working fine now and I can’t duplicate the problem either! Still don’t understand what happened, but thank you both for your help. And thanks, Andrew, for the tip about the calculate event; exactly what I needed.

Graeme

The underlying data doesn’t change; both it and the pivot table are refreshed on file-open. All that changes is the user selects a different week from the Week Number pagefield drop-down.
On a similar subject, when the table is changed the Worksheet_Change event doesn’t fire; is this the way it should work or am I missing something?

The table only changes when the user changes the page field using the dropdown; the page field is Week Number and the number of items in the row field changes from depending on which week the user selects. The background data doesn’t change. Still baffled!

Graeme

No, no blank entries. It seems to work ok sometimes, but if the number of rows increases when I change the table the range size doesn’t always increase to match. Deeply puzzling… The formula works perfectly outside pivot tables.

Thanks

Graeme

Edited by gwhitfield on 15-Nov-01 06:46.

Chip Pearson’s site has a good intro to pivot tables: http://www.cpearson.com/excel/pivots.htm. You could also look at John Walkenbach’s site or his Excel 2000 Bible.

Graeme

Not sure if I’ve understood correctly, but this would be one way to create a date list in a macro:

Sub FillDates()
Dim dtMin As Date
Dim dtMax As Date

dtMin = Application.WorksheetFunction.Min(Range(Range(“C3”), Range(“C65536”).End(xlUp)))
dtMax = Application.WorksheetFunction.Max(Range(Range(“D3”), Range(“D65536”).End(xlUp)))
With Range(“J4”)
.Value = dtMin
.DataSeries Rowcol:=xlColumns, Type:=xlChronological, Date:= _
xlDay, Step:=1, Stop:=dtMax, Trend:=False
End With
End Sub

HTH

Graeme

Wassim,
Thanks for your post. In the help for calculated items step 7 says:

“If the items were originally grouped and you ungrouped them in step 1, you can group them again or create new groups that include the calculated item, if you want.”

I do want, but I can’t! I’ve also checked the MSKB, which doesn’t offer any explanation.

Thanks again

Graeme

Exactly as ralphad says…
Also remember that xlLastCell doesn’t always give the results you might expect – especially if your data occupies a smaller range after refresh.

Graeme

You could try
Range(“A6″).CurrentRegion.Name=”MARDATA”
if there are no blank rows or columns in your range.

Graeme