correlating and summing data from multiple spreadsheets

[Peter]

Hi, bit of a tricky one here (or not?). I've got 10 Excel workbooks
(running 2007). Each workbook's got one relevant worksheet. Each
relevant worksheet has about 500 rows of line-items (expenses). Each
row is divided into a column, the most important one of which is, say,
Column C "Vendor ID". The rest is a bunch of expense data (amount,
description, etc.).

What I want to do is to sum up the totals for each Vendor ID, across
all 10 workbooks. What I've done so far is to simply Data/Sort each
workbook by Vendor ID. My next thought is to subtotal them - but that
still leaves me with the problem of adding each individual workbook's
Vendor ID totals together, for a 10-workbook per-Vendor ID grand
total.

The end product would ideally look like one column populated with each
Vendor ID, with a second column summing up the respective Vendor ID's
associated expense figures.

Does this make sense? I can move the 10 separate worksheets into 1
new multi-tab workbook, if that would help?

Thanks very much!

 

[Don Guillett]

Hi, bit of a tricky one here (or not?).  I've got 10 Excel workbooks
(running 2007).  Each workbook's got one relevant worksheet.  Each
relevant worksheet has about 500 rows of line-items (expenses).  Each
row is divided into a column, the most important one of which is, say,
Column C "Vendor ID".  The rest is a bunch of expense data (amount,
description, etc.).

What I want to do is to sum up the totals for each Vendor ID, across
all 10 workbooks.  What I've done so far is to simply Data/Sort each
workbook by Vendor ID.  My next thought is to subtotal them - but that
still leaves me with the problem of adding each individual workbook's
Vendor ID totals together, for a 10-workbook per-Vendor ID grand
total.

The end product would ideally look like one column populated with each
Vendor ID, with a second column summing up the respective Vendor ID's
associated expense figures.

Does this make sense?  I can move the 10 separate worksheets into 1
new multi-tab workbook, if that would help?

Thanks very much!

This can be done with a macro or even with formulas. You can leave in
separate files and have a macro get the data from each but it would be
preferred to NOT have links if using formulas. Best to put in sheets
in ONE file. If desired, send a complete expanation and file(s) to
dguillett1 @gmail.com