Excel Pivot Table questions 1

[MRBURGE]

As a company we hire agency workers, ive create a spreadsheet that will calculate how much is spent on them.
I have several sheets, one for each department.
Each sheet is made up of the fields, TempName, AgencyName, HourlyRate, HoursWork, TotalCost. Each sheet is exactly the same.

I have a summery page which has a pivot table for each sheet, the table shows the costs for each agency and total cost for that department.

So i have two questions.

1) is there any way that i can have the pivot tables update, for example, when the sheet is opened (VBA??) or on a click of 1 button, im trying to find a solution that means the less effort from the user, making it usable to all skill groups.

2)I need to have one final pivot table that shows the GRAND TOTAL of ALL the sheets, but when i select all the fields on all the sheets it still throws up problems and i can't reference cells on the summary page because as a new agency is added the position of the grand total for that sheet moves down.

Thanks in advance, and i appogise too if this question has already been asked.

 

[SkipVought]

Hi,

Second question first. You, either by inheritance of by choice, have made a mistake in the design of your workbook. If ALL your data were on a single sheet in a single table, the answer to this question would be VERY SIMPLE and could be done with a few keystrokes via a pivottable. You should almost never chop similar data up by some data element, in your case, AgencyWorker. So I'd STRONGLY recommend consolidating your data, which is professionally, the proper approch.

Then, in the pivot table table options, check to refresh on open.

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[MRBURGE]

DOES THE REFRESH WHEN OPEN, REFRESH WHEN THE FILE IS OPEN OR WHEN THE SHEET IS OPENED?

the idea of the one sheet per dept was that of my manager, but as u are not a fan of that idea would u suggest that i perhaps put a field name deptname and have it all on one sheet????

 

[dhulbert]

MrBurge

It refreshes the data when the workbook is opened.

I love deadlines. I like the whooshing sound they make as they fly by
Douglas Adams
(1952-2001)

 

[MRBURGE]

thanks, thats what i thought, but that still leaves me with the problem of refreshing the pivot table as a whole, either with VBA or a button????

Please help me, and if it can't be done then please tell me that so that i can change the design to help

 

[dhulbert]

Mr burge

You can use the .RefreshTable method to refresh the table, have a look in th eVBA help for the full syntax etc. All you need to do is to select each pivot and refresh it the code can be dropped into a button or added to the worksheet open event.
But
As skip said above you would probable be best entering all of your raw data into a single sheet in the workbook which makes a lot of other bits such as pivoting a hell of a lot easier, you cna then have summary sheets fo reach agancy if you want but it's all based on the master datasheet.




I love deadlines. I like the whooshing sound they make as they fly by
Douglas Adams
(1952-2001)

 

[SkipVought]

MB,

If you looked at the PT wizard, the checkbox clearly states "Refresh on open".

You OPEN a workbook.

You ACTIVATE a sheet. Sheets are never opened and closed.

If you have ALL your source data on one sheet, you can have as many PT's as you want on as many sheets as you want, and, therefore, have it your way!

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}