A
Andy
Hi im sure this isnt the easiest way to do my task so im asking if someone
could help me out here.
Task: create a chart for each workforce group with 3 series showing. Total
workers hours, part-time workers and fulltime workers against date.
I have a spreadsheet with 12 columns of data. I only use 4(hours workered,
date, employee id number, workforce group).
At the moment im making a macro to auto filter the workforce group so it
shows only 1 group then copy and paste the 3 columns of data i need to
another worksheet. It then sorts out the data according to ID number. I then
make a pivot table of the date(ROW) and SUM of hours worked(DATA). This pivot
tables give me the info to make the total hours worker series in the chart.
Now i need to get the info for part-time worker hours and fulltime worker
hours in the same format to make my graph. Now i sort the employee ID number
numerically. Any that start with a 0 or 1 is full time and 8 is part-time. So
i can copy and paste parttime and fulltime easily. But when i make a pivot
table for each of these they lack some dates due to only fulltimers worked on
a certain date and only some parttimers work on another. I copied all the
dates from the pivot table with the total cos that contains all the dates and
pasted it under the parttimers and full timers data and entered a 0 value in
the hours column. Now when i made the pivot table from this data it gave all
the dates.
What i would like to know is how to make a pivot table so date is on the
left column then for each date have a split cell saying part timer/fulltimer,
the corresponding hours worked next to it and total hours for each day.
Then making a chart from the pivot table would be the easiest correct?
Because i get new data every month and need to update the charts as well. Im
sure there is a much more simple way and if any1 could help it would be much
appreciated. THANX!
could help me out here.
Task: create a chart for each workforce group with 3 series showing. Total
workers hours, part-time workers and fulltime workers against date.
I have a spreadsheet with 12 columns of data. I only use 4(hours workered,
date, employee id number, workforce group).
At the moment im making a macro to auto filter the workforce group so it
shows only 1 group then copy and paste the 3 columns of data i need to
another worksheet. It then sorts out the data according to ID number. I then
make a pivot table of the date(ROW) and SUM of hours worked(DATA). This pivot
tables give me the info to make the total hours worker series in the chart.
Now i need to get the info for part-time worker hours and fulltime worker
hours in the same format to make my graph. Now i sort the employee ID number
numerically. Any that start with a 0 or 1 is full time and 8 is part-time. So
i can copy and paste parttime and fulltime easily. But when i make a pivot
table for each of these they lack some dates due to only fulltimers worked on
a certain date and only some parttimers work on another. I copied all the
dates from the pivot table with the total cos that contains all the dates and
pasted it under the parttimers and full timers data and entered a 0 value in
the hours column. Now when i made the pivot table from this data it gave all
the dates.
What i would like to know is how to make a pivot table so date is on the
left column then for each date have a split cell saying part timer/fulltimer,
the corresponding hours worked next to it and total hours for each day.
Then making a chart from the pivot table would be the easiest correct?
Because i get new data every month and need to update the charts as well. Im
sure there is a much more simple way and if any1 could help it would be much
appreciated. THANX!