S
!Sparky
WinXP, Excel 2000, Novice
I have a multi-sheet workbook that I use to track sales and driver earnings
for a small courier company (20 drivers). It works well for what I want it
to do, but as time goes on my requirements are changing. I don't want to
completely redesign my spreadsheet but I need to extract certain data from
one or more sheets to create a summary.
I actually figured out a way to achieve this from one sheet, but it's not
pretty and requires an intermediate sheet whose only purpose is to create
the basis for a pivot table. Let me explain.
One worksheet is called 'Brokerage' and lists the driver number, name, days
worked, sales, brokerage, paydate, etc. There are five 'Title' rows, the
driver list rows and a 'Totals' row at the bottom. This is repeated 52
times on the sheet to provide me with details for every week of the year.
It looks something like this:
Brokerage Report
Week of: Sept. 3, 2004 to Sept. 7, 2004
Week Number: 36
Week Driver Days #
Cheq. Cheq.
of # Name Work Runs Sales
Brok. Amount Date
----------------------------------------------------------------------------
-----------------
9/9/04 503 Smith 5 64 $804.50
$482.00 $454.00 9/27/04
9/9/04 506 Jones 4 52 $685.20
$403.50 $378.50 9/27/04
9/9/04 510 Harris 5 73 $812.95
$507.25 $492.30 9/27/04
etc.
Totals 14 189 $2302.65
$1392.75 $1324.80
Some of the title rows are merged cells and all information listed is
referenced from other worksheets in my workbook. As stated, this is
repeated 52 times (a total of about 1800 rows) to show each week of the
year. Drivers come and go so Harris, as shown in this sheet, may not have
existed two months ago and a new driver may be added in two weeks. Each of
the 52 sections shows only active drivers but each driver does have a unique
Driver #. There are worksheets for Sales, a Driver Info Table (rates of
remuneration, payroll deductions, etc) and others - each sheet exactly
matches, row by row, all of the others (i.e. if row 1200 in the Sales
worksheet shows Driver 503 in week 35, row 1200 in every other worksheet
shows information on Driver 503 in that week).
What I want to do is get a summary of driver sales, brokerage, days worked
etc. From this summary I can create average daily sales figures and a
variety of other reports. In the help file it looked like an array would
achieve what I wanted... =sum(if(B1:B1800=506,SUM(F1:F1800))) or something
like that, but it either returned 0 or 900,000 plus - I suspect the date
field in one of the merged title cells was the issue but I wasn't sure.
I then tried a pivot table but, after I selected the entire sheet I got a
message saying that the rows weren't appropriate. It was then that I got
the idea to create a new intermediate sheet that was exactly the same as my
Brokerage sheet (a1='Brokerage'!a1, etc), set the autofilter to show only
those rows where column B was greater than 500 but less than 600 (all driver
#'s fall within this range) then create a pivot table based on this sheet.
It works and I can imagine that I could add more references to the
intermediate sheet to show information from any worksheet in my workbook,
but it seems silly to require an intermediate sheet rather than extract the
required data directly from the source sheets.
I apologize for such a lengthy message, but any suggestions would be
appreciated.
Thanks in advance.
I have a multi-sheet workbook that I use to track sales and driver earnings
for a small courier company (20 drivers). It works well for what I want it
to do, but as time goes on my requirements are changing. I don't want to
completely redesign my spreadsheet but I need to extract certain data from
one or more sheets to create a summary.
I actually figured out a way to achieve this from one sheet, but it's not
pretty and requires an intermediate sheet whose only purpose is to create
the basis for a pivot table. Let me explain.
One worksheet is called 'Brokerage' and lists the driver number, name, days
worked, sales, brokerage, paydate, etc. There are five 'Title' rows, the
driver list rows and a 'Totals' row at the bottom. This is repeated 52
times on the sheet to provide me with details for every week of the year.
It looks something like this:
Brokerage Report
Week of: Sept. 3, 2004 to Sept. 7, 2004
Week Number: 36
Week Driver Days #
Cheq. Cheq.
of # Name Work Runs Sales
Brok. Amount Date
----------------------------------------------------------------------------
-----------------
9/9/04 503 Smith 5 64 $804.50
$482.00 $454.00 9/27/04
9/9/04 506 Jones 4 52 $685.20
$403.50 $378.50 9/27/04
9/9/04 510 Harris 5 73 $812.95
$507.25 $492.30 9/27/04
etc.
Totals 14 189 $2302.65
$1392.75 $1324.80
Some of the title rows are merged cells and all information listed is
referenced from other worksheets in my workbook. As stated, this is
repeated 52 times (a total of about 1800 rows) to show each week of the
year. Drivers come and go so Harris, as shown in this sheet, may not have
existed two months ago and a new driver may be added in two weeks. Each of
the 52 sections shows only active drivers but each driver does have a unique
Driver #. There are worksheets for Sales, a Driver Info Table (rates of
remuneration, payroll deductions, etc) and others - each sheet exactly
matches, row by row, all of the others (i.e. if row 1200 in the Sales
worksheet shows Driver 503 in week 35, row 1200 in every other worksheet
shows information on Driver 503 in that week).
What I want to do is get a summary of driver sales, brokerage, days worked
etc. From this summary I can create average daily sales figures and a
variety of other reports. In the help file it looked like an array would
achieve what I wanted... =sum(if(B1:B1800=506,SUM(F1:F1800))) or something
like that, but it either returned 0 or 900,000 plus - I suspect the date
field in one of the merged title cells was the issue but I wasn't sure.
I then tried a pivot table but, after I selected the entire sheet I got a
message saying that the rows weren't appropriate. It was then that I got
the idea to create a new intermediate sheet that was exactly the same as my
Brokerage sheet (a1='Brokerage'!a1, etc), set the autofilter to show only
those rows where column B was greater than 500 but less than 600 (all driver
#'s fall within this range) then create a pivot table based on this sheet.
It works and I can imagine that I could add more references to the
intermediate sheet to show information from any worksheet in my workbook,
but it seems silly to require an intermediate sheet rather than extract the
required data directly from the source sheets.
I apologize for such a lengthy message, but any suggestions would be
appreciated.
Thanks in advance.