Using a pivot table in a Holiday Calander

Topic

New Reply

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.

Reply | Quote

Replies

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.

Reply | Quote

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.

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

Reply | 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

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

Reply | Quote

[Tom Duthie
[/quote]

Here is the attachment

Tom Duthie

Reply | Quote

[Tom Duthie

Here is the attachment

Tom Duthie

Thanks for that Tom

I will go and have a play

Reply | Quote