Results 1 to 6 of 6
  1. #1
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Good morning

    I have over the last couple of weeks realised how sexy pivot tables are and wondered how I could use them to improve my absence scheduler. I mainten this schedule for about 8 offices / 130 staff world wide and it includes all type of absences, Holiday, Sickness, Business, maternity etc.

    I have mocked up a non working example with a bit of dummy information and attached it and wondered how, if it is possible, that I could make this into a pivot table.

    My efforts so far have produced either all of the dates 1 - 31 in the table but not the staff, departments, months etc. and if I try to include them it says that the table is not set out correctly.

    If it can't be done with my layout can anybody advise how the best layout to achieve a good result.
    Attached Files Attached Files
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Pivot tables are used to display aggregated data - sums, counts, averages etc. They can *not* be used for data entry, and they can *not* be used for displaying text values. So a pivot table is not suitable for an absence schedule.

  3. #3
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='778951' date='09-Jun-2009 12:48']Pivot tables are used to display aggregated data - sums, counts, averages etc. They can *not* be used for data entry, and they can *not* be used for displaying text values. So a pivot table is not suitable for an absence schedule.[/quote]

    Thanks Hans

    Thats a shame, I had visions of being able to show by employee, department, Country etc. with a simple click, but never mind I will stick with what I have

    Cheers

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  4. #4
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    New York, New York, USA
    Posts
    266
    Thanks
    0
    Thanked 19 Times in 19 Posts
    [quote name='stevehocking' post='779064' date='10-Jun-2009 04:35']Thanks Hans

    Thats a shame, I had visions of being able to show by employee, department, Country etc. with a simple click, but never mind I will stick with what I have

    Cheers

    Steve[/quote]

    I agree with Hans. And the Table sample you provided with its colors and shading would be almost impossible to reproduce in a Pivot Table.

    But if your data is arranged in a similiar fashion to the attached and you could change your Letters to Numbers such as B = 1 H = 2 etc then the attached Pivot Table could be used. I did not include the days such as Monday, Tuesday, but that could be added by creating a lookup table and then a vlookup formula to get the Day. It could then be added as a Column Data Item in the Pivot Table.

    Please note that the Pivot Table uses the PAGE function that allows the usser to limit the data to a particular month or show all months.

    Hope this may help in your considerations.

    Regards,

    Tom Duthie

  5. #5
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    New York, New York, USA
    Posts
    266
    Thanks
    0
    Thanked 19 Times in 19 Posts
    [Tom Duthie
    [/quote]

    Here is the attachment

    Tom Duthie
    Attached Files Attached Files

  6. #6
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='duthiet' post='779104' date='10-Jun-2009 15:29'][Tom Duthie


    Here is the attachment

    Tom Duthie[/quote]

    Thanks for that Tom

    I will go and have a play
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •