ExcelBanter - View Single Post

supoch14

"Max" wrote:

Try a pivot table (PT). It'll get you the results you seek in a matter of
seconds

Select any cell within your source table.
Click Data Pivot table ...
Click NextNext.
In step 3, click Layout
Drag n drop Workshop within the Row area.
Drag n drop Invoice date within the Row area, below Workshop.
Drag n drop Amount within the Data area.
Click Ok Finish

Go to the PT sheet
Right-click on any date in the Invoice date col Group & show detail Group
Accept the auto settings to group by "Months" OK
Then just drag n drop "Invoice date" over "Total"
That's it. The pivot will return the desired results for (1)

And to get results for (2), just make a copy of the pivot sheet for (1),
then replace "Workshop" with "Lorry No."

In the copy of the pivot table sheet,
right-click on any cell in the PT choose "Pivottable wizard"
Click Layout. Drag n drop "Workshop" out of the Row area (remove it). Then
just drag n drop "Lorry No." within the Row area (to replace Workshop). Click
OK Finish. This would return the results you seek for (2).
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"vcff" wrote:
any help pls?

"vcff" wrote:

Hi All

I need to gather some info regarding our vehicle maintenance.
I have a worksheet which include the following details under seven columns
and details will be keyed in when vehicle sent for servicing.

A1, B1, C1, D1, E1, F1, G1,
Workshop, Date of service, Lorry No., Invoice No., Invoice date, Amount,
Remark

I need to have two reports in another worksheet base on the abve info.
1. the list of workshops with the "total amount" charged under one month.

workshop Jan Feb Mar Apr .......

2. the list of vehicle with the "total amount" charged under the same month.

Lorry No. Jan Feb Mar Apr MAy .......

tnks in advance for the help