Pivottables? Queries? Lookups, or something else??

[#VALUE]

Hi all,

Long time reader, first time poster... I consider myself a fairly advanced excel user but I'm struggling with the most effective 'setup' for my current project.

Basically, my boss has asked me to try and put together a report to consolidate various statistics on staff here in the office (approx 200 staff) into a single 'view'. The measures that she's asked me to include are all available, but come from a variety of different sources and in different formats.

For example, one exportable data source contains data with the following columns (about 26 columns in all, but many are not necessary):

Spreadsheet 1: Employee ID, Date of Survey, Survey Result (Satisfaction out of 5), Enquiry Resolved (1/0 corresponding to yes/no), Team Name.


Examples of the data that could be in each column:

1234, 02/03/2011 (dd/mm/yyyy), 4, 1, "Legends"

There could be multiple surveys carried out on the same day for a particular staff member. The "central view" I need to create is basically like a PivotTable, e.g. pick a date and see the average result for that employees survey ratings for that day, or pick no date and see the average across all data.

The problem is, I need this "central view" to basically pivot not only the data from their survey results, but the data from say, their sales results for the month. Because each source of data (sales, surveys etc.) exports in a different format, with different column headings, it isn't possible to consolidate these results into a single pivot table.

At the moment, the work around I have is to have "Sheet 1" with a drop down-list allowing the selection of "Whole Month", or a specific date. There is a list of employees and their corresponding employee IDs, together with a modified (abbreviated) employee ID (some of the exported reports use this value), followed by column headings for each data field that is of interest.

Pic of my 'Sheet 1' here:



The only way I've found to have the data presented how I would like it to be presented, is to build pivot tables for each sheet of source data, and then use "GetPivotData" to retrieve the data that I'm looking for, but I'm sure there has to be a better way.

Any advice, input, suggestions would be greatly appreciated!

Thanks,

 

[Fazza]

Hi,

I can't see the image. I'm a little unclear on the requirement.

It sounds like there are multiple tables of data and you want a report basically with data from each one. Perhaps in a pivot table. I'll assume there is a common key such as employee ID or name in every table.

ASSUMING the data is reasonably wll set up, can you work like a database? So set up a query or two to pull data from the various tables, either directly into the final pivot table or, cause it would be simpler, into a new table or two. And then have the final pivot table/s from the/se final table/s.

Likely it would be helpful to have some VBA. Maybe create pivot table report/s on demand in a separate file.

What do you think?