Linking Multiple Workbooks

[Big Ben]

Hi Guys,

I have 40 different workbooks representing the sales for 40 differen
regions. Each region has identical spreadsheets. I want to make
summary sheet of total sales and related expenses. It is not possibl
to have all regions in the same workbook under different tabs.
therefore need to do a sum whereby the sum function is linked to 4
workbooks. I have all workbooks located in one folder. Is there an
way to make this sum formula easier so that I do not have to go int
each workbook and like the cell to the sum function.

=[test1.xls]Sheet1!$B$3 + [test2.xls]Sheet1!$B$3
[test3.xls]Sheet1!$B$3 + [test4.xls]Sheet1!$B$3
[test5.xls]Sheet1!$B$3 + [test6.xls]Sheet1!$B$3
[test7.xls]Sheet1!$B$3 + [test9.xls]Sheet1!$B$3.........etc.....


I am thinking it may look something like follows but I am not to
sure....also, my workbooks are named after cities.

=sum([test1.xls]:[test3.xls]Sheet1!$B$3

Thanks in advance

 

[DDM]

Big Ben, this would be a great formula
=sum([test1.xls]:[test3.xls]Sheet1!$B$3), but it won't work. As an
alternative, I would suggest that (1) you define names for the relevant
cells on each of the 40 workbooks. So $B$3 on Sheet1 of test1.xls can be,
say, Total. Same name for the corresponding cells on each of the other 39
workbooks. Then (2) in the summary workbook, I would create a helper
worksheet, and on it I would make a list of linked cells, so:

=test1.xls!Total
=test2.xls!Total
....
=test40.xls!Total (note how naming the cells makes this entry easier)

....and I would name this list. Let's call it Totals. You can hide the helper
worksheet if you want.

Now here's the payoff: Your final formula is =SUM(Totals).

 

[Harlan Grove]

. . . I would suggest that (1) you define names for the relevant
cells on each of the 40 workbooks. So $B$3 on Sheet1 of test1.xls
can be, say, Total. Same name for the corresponding cells on each
of the other 39 workbooks. Then (2) in the summary workbook, I
would create a helper worksheet, and on it I would make a list of
linked cells, so:

=test1.xls!Total
=test2.xls!Total
...
=test40.xls!Total . . .

...and I would name this list. Let's call it Totals. You can hide
the helper worksheet if you want.

Now here's the payoff: Your final formula is =SUM(Totals).

Why not just use Data > Consolidate... ?

 

[DDM]

Righto, Harlan! Data > Consolidate is another possible approach. There's
more than one way to skin a cat.

 

[DDM]

....although consolidating the data could cause problems if Big Ben has more
than one cell or range on the worksheet he wishes to consolidate.

 

[Harlan Grove]

...although consolidating the data could cause problems if Big Ben
has more than one cell or range on the worksheet he wishes to
consolidate.

....

Could be handled with macros. Also, using Data > Consolidate with Create
links to source data would add all the link formulas you had recommended the
OP use. Sometimes simpler is better.