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 Next>Next.
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
> >
> >
Similar Threads
