Subtotals 1

[matpj]

I need to perform two subtotals at once - is this possible:


three columns:

Event name, Service Ordered, Quantity

I need to be able to total each type of service ordered by event. i.e. at each change in Event Name, perform an 'at each change in 'Service Ordered' SUM 'quantity''

can anyone help?

 

[ilses]

Presuming you want to do an ordinary Data->Subtotals thing:

Sort your list as follows (guess you got this already, but OK)
Sort by: EventName
Then by: Service Ordered

Then go to Data -> Subtotals
At each change in: EventName
Use function: Sum
Add subtotal to: Quantity.
Check Replace current totals and Summary below data.
Press OK. (I bet you got that already too.)

Then go to Data->Subtotals again.
At each change in: Service Ordered
Use function: Sum
Add subtotal to: Quantity
This time uncheck Replace current totals, leave Summary below data checked.
Press OK. (Guess how often I forgot that one.)

IS

 

[matpj]

Thanks, that helped a great deal.

I have another question - this, I'm sure is not possible in Excel:

Once I have the data returned by the two subtotal processes, it looks similar to this:


Analogue Line 2
IP Phone 4
ISDN Line 1
Internet Accounts 5
Gadget Event 12

etc

This is listed down with for each event. Not all events will have everything ordered, some will just have a few of one type of service etc.
I want to now be able to tabulate the data, so that it will look like this:


Analogue Line IP Phone ISDN line
Gadget Event 2 4 1



is this in anyway possible? do I have to do something to it before I do the subtotal?

 

[ilses]

Matpj,

Uh, no - it is possible! Forget the Subtotals thing though, and turn to Pivot Table. You'll find it on the same Data menu. Remove any subtotals you may have in place before you start. Keep your columns as they are (EventName, ServiceOrdered, Quantity), unsorted if you like. Select the range of data plus the labels of your columns. To select the entire list plus labels press Ctrl+*. Go to Data, PivotTable. A wizard will guide you through the next steps. Clicking Next and finally Finish, usually does the job. To start, I suggest you leave the Page field blank, drag the EventName field to the Row field column on the left, drag Service to the column field and drag Quantity to the Data field.
This reads as if it's awfully complicated, but when you see it on screen, it's easy (more so in Excel 2000 than in 97, I don't think 95 has Pivot Tables).

Good luck!

Ilse

 

[matpj]

I am having problems writing a macro for the pivot table.
when I run it, it displays an error saying:
Run Time Error '1004':
Add fields method of Pivot Table class failed.

do you know why this might happen?

 

[ilses]

Yes and no. Error 1004 is pretty generic. In general it means that the method cannot be used on the object. You may have used an argument with an invalid value. Wrong index or name of Pivot Table in the expression part of expression.AddFields is a not unlikely candidate. Without the code, however, it's a shot in the dark.

Perhaps posting to the VBA forum with the relevant pieces of code may get you a better answer.

Ilse

 

[matpj]

Ok,

just on the off chance, here is my code - I'm not sure how to get bold text in these forums, but the error is about half way down, at the Addfields section:


Sub Macro2()
'
' Macro2 Macro
' Macro recorded 10/07/2001 by EBMS.Support
'
' Keyboard Shortcut: Ctrl+s
'
With ActiveSheet.QueryTables.Add(Connection:= _
"FINDER;X:\EBMS_Q\Future Event Services.dqy", Destination:=Range("A1&quot)
.Name = "Future Event Services"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
Cells.Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"Sheet1!C1:C3&quot.CreatePivotTable TableDestination:=Range("D1&quot, TableName:= _
"Future Services"
ActiveSheet.PivotTables("Future Services&quot.SmallGrid = False
ActiveSheet.PivotTables("Future Services&quot.PivotCache.RefreshOnFileOpen = True
ActiveSheet.PivotTables("Future Services&quot.AddFields RowFields:= _
"EV200_EVT_DESC", ColumnFields:="ER101_DESC"
ActiveSheet.PivotTables("Future Services&quot.PivotFields("ER101_RES_QTY&quot. _
Orientation = xlDataField
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 1
ActiveWindow.ScrollColumn = 15
ActiveSheet.PivotTables("Future Services&quot.PivotFields("Count of ER101_RES_QTY" _
).Function = xlSum
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 1
ActiveSheet.PivotTables("Future Services&quot.RefreshTable
Application.CommandBars("PivotTable&quot.Visible = False
End Sub


maybe if you could tell me how to have bold text I can then post it to someone on the VB forum!

thanks for all your help
Matt

 

[ilses]

If I read the Help correctly, you'd first need to have a Pivot Table in place to use AddFields.

I've happily ignored the first part of your code (but see below) in the following and cleaned up a bit:

Sub PivotTest()
'
Dim NewSheet As Worksheet
Dim strPivotSheet As String

Set NewSheet = Worksheets.Add
NewSheet.Name = "Pivot_" & Worksheets.Count + 1

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"Blad1!A1:C8&quot.CreatePivotTable TableDestination:=NewSheet.Range("A1&quot, TableName:= _
"Future Services"
With ActiveSheet.PivotTables("Future Services&quot
With .PivotFields("EV200_EVT_DESC&quot
.Orientation = xlRowField
.Position = 1
End With
With .PivotFields("ER101_DESC&quot
.Orientation = xlColumnField
.Position = 1
End With
With .PivotFields("ER101_RES_QTY&quot
.Orientation = xlDataField
.Position = 1
End With
End With

Application.CommandBars("PivotTable&quot.Visible = False

End Sub

This works with an Excel list/database but would need more work, especially the hardcoded range for the DataSource is problematic. I don't know what "dbq" files are, but I guess you're setting up a query from a database. Check out the VBA Help on DataSource, from which I took the following sample:

Set newSheet = ActiveWorkbook.Worksheets.Add
sdArray = Worksheets("Sheet1&quot.UsedRange.PivotTable.SourceData
For i = LBound(sdArray) To UBound(sdArray)
newSheet.Cells(i, 1) = sdArray(i)
Next i

Allow me to add some cautionary advice: Do not use Ctrl+S as shortcut, it overrides Excel's Ctrl+S (Save).

Bold text: click Preview Post and at the bottom there's link to a list of mark-up codes.

Ilse