M
Michael Bayuk
My thanks to Dave Peterson who suggested the use of Outlook Express and
Microsoft Newsgroups to post this message.
I have an Excel list with 28 columns. Column headings include such items as
Name, Hire Date, Termination Date, Next Review Date, Hourly Rate, Increase,
Future Rate, Worked Hours, Total Hours, Location1, Hours1, Location2,
Hours2,...Location8, Hours8. I would prefer to manage this list where the
information for each employee is contained on only one row. The Location
and Hours number (i.e., Location2, Hours2) is irrelevent except to provide
for certain employees working at more than one location during the year. My
goal is to generate one pivot table from an efficient list that lists all of
the detail for each employee by location. Thus, certain employees will
appear more than once if they work at multiple locations.
For example, the column headings of the pivot table results would look
something like this:
Location Name Review Date Hourly Rate Increase
etc... Hours
The following alternatives that I know would generate such a pivot table are
unacceptable for various reasons:
1) Insert additional multiple rows for those employees who work at
multiple locations and thus eliminate the multiple Location Hours columns.
Managing (i.e., inserting/deleting/copying information from row to row, then
editing it) such multiple rows by non-expert Excel data entry persons to a
protected worksheet would be a disaster.
2) Insert (and hide) all of the other detail columns before each pair of
Location/Hours with formulas equal to the detail in the original columns,
and use the Multiple Consolidation Ranges type of pivot table. This would
needlessly increase the file's size and calculation time for the hundreds of
employees listed.
I tried unsuccessfully to group the Locations. Perhaps there is a way to
group the Locations so that they will roll up under one field "Locations"
with the corresponding field "Hours"? Perhaps there is another alternative?
Please help!
MikeB
Microsoft Newsgroups to post this message.
I have an Excel list with 28 columns. Column headings include such items as
Name, Hire Date, Termination Date, Next Review Date, Hourly Rate, Increase,
Future Rate, Worked Hours, Total Hours, Location1, Hours1, Location2,
Hours2,...Location8, Hours8. I would prefer to manage this list where the
information for each employee is contained on only one row. The Location
and Hours number (i.e., Location2, Hours2) is irrelevent except to provide
for certain employees working at more than one location during the year. My
goal is to generate one pivot table from an efficient list that lists all of
the detail for each employee by location. Thus, certain employees will
appear more than once if they work at multiple locations.
For example, the column headings of the pivot table results would look
something like this:
Location Name Review Date Hourly Rate Increase
etc... Hours
The following alternatives that I know would generate such a pivot table are
unacceptable for various reasons:
1) Insert additional multiple rows for those employees who work at
multiple locations and thus eliminate the multiple Location Hours columns.
Managing (i.e., inserting/deleting/copying information from row to row, then
editing it) such multiple rows by non-expert Excel data entry persons to a
protected worksheet would be a disaster.
2) Insert (and hide) all of the other detail columns before each pair of
Location/Hours with formulas equal to the detail in the original columns,
and use the Multiple Consolidation Ranges type of pivot table. This would
needlessly increase the file's size and calculation time for the hundreds of
employees listed.
I tried unsuccessfully to group the Locations. Perhaps there is a way to
group the Locations so that they will roll up under one field "Locations"
with the corresponding field "Hours"? Perhaps there is another alternative?
Please help!
MikeB