Consolidating multiple worksheets

**Prium** · 09-28-2008, 09:51 PM

Data mining problem:

Row totals exceed the limit for Excel 2003, so I have split the data into different worksheets by year.

Problem is, now I can't create a useful pivot table, i.e. using 'multiple consolidation ranges' reduces the field list to "Row", "Column", and "Value".

I have 26 columns in each worksheet (all identical structures); some are multilevel factors, some are variables - and the way these relate to each other is unknown as this stage, so I literally cannot have the dimensions reduced in the way Excel proposes with multiple consolidation ranges.

Am I limited to creating individual pivots tables for each worksheet, or is there a better way?

Cheers

**m4tty** · 09-29-2008, 11:45 AM

Would a database such as Access not be a better place to store your data considering the size? You could then perform your analysis stuff from Excel on the Access database.

**Prium** · 09-29-2008, 08:26 PM

I haven't had any experience linking Excel to external sources, other than trying to link to another Excel sheet - which didn't work because xls files weren't an option I could select from 'data sources'

**m4tty** · 09-30-2008, 04:16 AM

What you need to do (depending on which version of Excel you have) is go to Data > Import External Data > Import Data which should bring up the attached screen.

From here you will be able to import the data from an Access spreadsheet or any other database you have installed. If you don't have Access installed and your department won't pay for the licence, there are free alternatives such OpenOffice that allow you to save in .mdb format (the format used by Access) which Excel works most easily with.

If you want to stick with Excel then there might be some manipulation you can but I'll have to have a look and work it out. Let me know about the database and I'll come back to you as soon as possible.

**m4tty** · 09-30-2008, 04:17 AM

Wait... ignore the last post.

**m4tty** · 09-30-2008, 04:20 AM

Rather than use Data > Import External Data, use Data > PivotTable and then click External Data Source which will allow you to perform a pivottable directly on the database rather than having to import the data which you can't do because there will be too many lines. Was having a dumb moment

**steven1001** · 10-02-2008, 12:39 AM

If you don't want to store your data in a separate database product then, in a new spreadsheet, you can access the data in the existing spreadsheet with MSquery and use a union command to select the data from multiple worksheets into a pivot table in the new spreadsheet.