Is it possible to combine two worsheets into one Pivot Table?

[Diane13]

Using Excel 2003. I have two Excel files, each with a single worksheet
containing the data that I need to summarize. Each has a common field
containing an account number. I need to summarize data from each spreadsheet
into a single table. For example, I have account number, customer name, city
and state in one worksheet/file and dollar amounts on about 10% of those
customers in the other worksheet/file. Is it possible to combine this data
into a single Pivot Table from the two files? Or do I need to combine the
files into a single worksheet first and go from there? I am new to working
with Pivot Tables and do not know all the ins and outs of the feature.
Thanks for any help you can provide!

[FSt1]

hi,
yes it is possible. you need to declare each data range in both files as a
named range then use micorsoft query to combine the two on a seperate sheet
using the common account number field as the link. You can then use the
combined table for you pivot table. if you are unfamilar with named
ranges(tables) and micorsoft query, i suggest you read up on both in ms help.
I think that is the way you need to go.
regards
FSt1

"Diane13" wrote:

> Using Excel 2003. I have two Excel files, each with a single worksheet
> containing the data that I need to summarize. Each has a common field
> containing an account number. I need to summarize data from each spreadsheet
> into a single table. For example, I have account number, customer name, city
> and state in one worksheet/file and dollar amounts on about 10% of those
> customers in the other worksheet/file. Is it possible to combine this data
> into a single Pivot Table from the two files? Or do I need to combine the
> files into a single worksheet first and go from there? I am new to working
> with Pivot Tables and do not know all the ins and outs of the feature.
> Thanks for any help you can provide!
>

[Diane13]

Sorry for the delayed reply back to you. Your solution worked fine except I
now have some files that are over 65K lines and too large for Excel. I am
now trying to make sense of Access tables and relationships and will look
toward that user forum for more assistance. Thank you, FSt1!


"FSt1" wrote:

> hi,
> yes it is possible. you need to declare each data range in both files as a
> named range then use micorsoft query to combine the two on a seperate sheet
> using the common account number field as the link. You can then use the
> combined table for you pivot table. if you are unfamilar with named
> ranges(tables) and micorsoft query, i suggest you read up on both in ms help.
> I think that is the way you need to go.
> regards
> FSt1
>
> "Diane13" wrote:
>
> > Using Excel 2003. I have two Excel files, each with a single worksheet
> > containing the data that I need to summarize. Each has a common field
> > containing an account number. I need to summarize data from each spreadsheet
> > into a single table. For example, I have account number, customer name, city
> > and state in one worksheet/file and dollar amounts on about 10% of those
> > customers in the other worksheet/file. Is it possible to combine this data
> > into a single Pivot Table from the two files? Or do I need to combine the
> > files into a single worksheet first and go from there? I am new to working
> > with Pivot Tables and do not know all the ins and outs of the feature.
> > Thanks for any help you can provide!
> >