Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
create a summary record
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
create a summary record
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
create a summary record
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
create a summary record
Hi Max
Tnks for the help. It solved my problem. As this is my first time using pivot table, I found that it is indeed a very good program. However, may need more time to get used to it (slow learner). Have A Nice Day "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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
create a summary record
Welcome. Thanks for feeding back.
-- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "vcff" wrote in message ... Hi Max Tnks for the help. It solved my problem. As this is my first time using pivot table, I found that it is indeed a very good program. However, may need more time to get used to it (slow learner). Have A Nice Day |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
create a summary record
"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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
create a summary record
"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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Create Summary List Page | Excel Discussion (Misc queries) | |||
not able to create subtotal summary report | Excel Worksheet Functions | |||
Create a summary list without using PivotTables? | Excel Worksheet Functions | |||
how do i create a record sheet | New Users to Excel | |||
How to create summary of expense report? | Excel Worksheet Functions |