I have created a workbook and save it as a differant file name each time. How
do I collect data from multiple those workbooks and collect it in a sumary
workbook? The sumarry workbook will be the running total for the year. I've
been typing formula strings and am getting no where. HELP : (
--
Bill Needham
GSSComputers

Bill,
Almost easier to show you than to tell you.

Open the workbook that you want to contain the running total. Let's call
that MASTER. Open one of the other workbooks. We'll set up one formula now.

In the MASTER, choose the cell you want the formula/running total of some
group to appear in, start by typing in an = symbol. Choose the other
workbook and choose the sheet and cell you want to be part of the formula in
MASTER. Press the [Enter] key.

You now have the start of a formula to do rollups, referring to one other
workbook. Open another "other" workbook, go back into MASTER to the cell you
were working with, at the end of the formula that's in there now, type a +
symbol, and once again choose the other workbook, sheet and cell to add to
the running total value.

Pretty much repeat that for all values to be included in running totals.
The initial setup can be a bit tedious, depending on how many of those Other
workbooks you have. But after that you just have to add in the latest
workbook.

Couple of hints:
IF your cells to be dealt with are in sequential rows, you can remove the $
signs in front of cell addresses in the formula and copy/fill the formulas
down in the MASTER book and they'll adjust automatically.

Don't have the MASTER open when you rename a workbook that's already in the
formula. Either make sure the master is closed or just make a copy of the
'other' workbook and rename it first. That'll keep your formulas from
changing workbook name reference.

Look at how the formulas are constructed - it may be easier to type in
additional workbook references as time goes on rather than going through the
manual process. Notice the difference in the formulas when an "other"
workbook is open and when it is closed. Excel automatically adds/removes the
full path to the workbook in the formulas as the other workbook is
opened/closed.

"Bill Needham" wrote:

I have created a workbook and save it as a differant file name each time. How
do I collect data from multiple those workbooks and collect it in a sumary
workbook? The sumarry workbook will be the running total for the year. I've
been typing formula strings and am getting no where. HELP : (
--
Bill Needham
GSSComputers

See Ron de Bruin's site for code assistance.

http://www.rondebruin.nl/tips.htm

Browse through the section on Copy/Paste/Merge examples


Gord Dibben MS Excel MVP

On Thu, 10 Jan 2008 22:15:21 -0800, Bill Needham
wrote:

I have created a workbook and save it as a differant file name each time. How
do I collect data from multiple those workbooks and collect it in a sumary
workbook? The sumarry workbook will be the running total for the year. I've
been typing formula strings and am getting no where. HELP : (