Excel Pivot Table using Multiple Worksheets 1

[davidmo]

Hey Everyone:
I need to create a pivot table that uses information from 3 different worksheets. The pivot table is a Year-to-date Financial report.

I am using Excel 2002 and tried to use the Pivot Table Wizard's "Multiple Consolidation Range" function. A complete bust.

The 3 worksheets all have the same column headings and are in the same format. Unfortunately I can't combine them on 1 worksheet because the size will be greater than 65,536 lines by the 3rd and 4th quarters.

More than willing to use VB to create a solution.

I inherited this workbook just the other day and need to have financials done in 2 work days.

Thanks.

DMo

 

[SkipVought]

Hi,

Use External data source instead of Multiple consolidation ranges

Query the other sheets as a UNION query.

Skip,
[sub]
[red][/red]
[/sub]

 

[davidmo]

Hey Skip,

I've never used a UNION query. How do you go about creating one? Is there a site I can read about them?

DMO

 

[SkipVought]

It joins by APPENDING data in like formats

Select Name, City From Sheet1
union
Select Name, City From Sheet2
union
Select Name, City From Sheet3

Skip,
[sub]
[red][/red]
[/sub]

 

[davidmo]

Thanks Skip

Will try this and let you know.

 

[KenWright]

Or if you have MS Access, just dump all 3 sets of data one on top of the other into a single Access Table and then use Excel's Pivot table to query the Access file. No different to using a single sheet in Excel.

Assuming you want to give it a go, fire up MS Access and hit

File / New / Blank Database, give it a name and save it.

Click on 'Create Table by using Wizard' and then hit the icon marked 'New'

Click on 'Import Table' / OK

Change Files of Type to Microsoft Excel (*.xls) and navigate to where your Excel file is. Click on it and hit Import.

It will now show you your worksheets, so just use the first one and hit NEXT. Make sure that 'First Row contains Headings' is ticked and then hit NEXT.

Choose 'In a New Table' and hit FINISH.

Now double click the sheet that just appeared in your MS Access window and you should see your data.

Now just repeat for the other two sheets, but instead of choosing the option 'New Table', choose 'Into Existing table', so............




Click on 'Create Table by using Wizard' and then hit the icon marked 'New'

Click on 'Import Table' / OK

[Shouldn't need to do this bit as should still be selected]
Change Files of Type to Microsoft Excel (*.xls) and navigate to where your Excel file is. Click on it and hit Import.

It will now show you your worksheets, so now select the second one and hit NEXT. Make sure that 'First Row contains Headings' is ticked and then hit NEXT.

Choose 'IN AN EXISTING TABLE', select your table (Will be the sheet name from your first sheet) from the dropdown and hit FINISH.

Now double click your sheet in the MS Access Window again and you should see all your data.


From here, you now simply go Into Excel, use Data / Pivot table and Chart report, Click on 'External data Source' and hit next.

Click on 'Get Data', choose 'MS Access Database*' and hit OK.

Browse to your Access database. click on it and hit OK.

Now hit the Right arrow that you can see in the dialog box to shunt everything over from the left siod eof the window to the right, and then hit NEXT / NEXT / NEXT,a nd as long as 'Return Data to Microsoft Excel' is selected, just hit FINISH.

This takes you back to a dialog box, and you now just hit NEXT / 'NEW WORKSHEET" / FINISH.

Done, and you are back in your comfort zone with Pivot Tables as you understand them.

Regards
Ken............










----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------