Rollup data from multiple excel sheets

[ncramgopal]

Dear Experts,
I am sure you will be able to help me find a solution to my problem. I have KPIs in excel sheets. I have four sets of excel workbooks corresponding to KPIs measured across seven departments- one for each quarter of the year 2012. Each Quarters KPIs are rolled up to provide department-wise KPIs.
Now, I need to consolidate these summary sheets to provide a consolidated Annual KPI sheet. Furthermore, I need to get a trend of the KPIs across the four quarters.
How do I achieve this?
My main problem is the consolidation for the annual report.

Please help.

[arlu1201]

Do you have a sample file that you can upload?

To Attach a File:

1. Click on Go Advanced
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.

[ncramgopal]

Dear arlu1201
Thank you for your response. I am attaching herewith the sample sheet. This is how my current excel workbook looks like after consolidating the four quarters of KPIs. There are two things I need to do:
1. Aggregate all the results into an annual summary
2. Plot the trend across the KPIs against departments (I tried using pivot charts but somehow it didn't just give me the feel I was hoping). What I did was copy pasted the values in the analysis sheets of each quarter into a separate sheet and created a pivot table. But, I was hoping to do this just by referring to the KPIs across the sheets. Please comment.

Look forward to your response- eagerly!

Best Regards