Shorten Links in Formulas

I have a series of Workbooks (20+) each contain very detailed worksheets (~42). I need to provide a summary in one workbook. Each workbook was sort of a template, so the file names, tabs and calculations are all common. Each workbook is in a separate directory. I am trying to sum all 20 sheets in one sheet, but I run into a problem with the formula being to long.

ie c:\dir1\filename.xls![input tab] E$1$+c:\dir2\filename.xls![input tab] E$1$ .
How do I create a name to shorten the link that can be copied throughout the workbook?
=name[input tab]$E$1+name2[input tab]$E$1. I have tried to setup a separate sheet with the root of each file in a cell and then try to combine this cell with the rest formula with no luck. Any suggestions?

If I've understood you, the summary you want means adding up all the detail
sheets. If that's the case then this file might be a help:
http://www.bygsoftware.com/examples/zipfiles/consol.zip
It's in the "Accountants" section on page:
http://www.bygsoftware.com/examples/examples.htm

The "Bread-Roll" consolidation method - great for accountants. See how
simple it is to consolidate any combination of your organisation's accounts.
(No VBA used)

--
Regards
Andy Wiggins
www.BygSoftware.com
Home of "Save and BackUp",
"The Excel Auditor" and "Byg Tools for VBA"



"MJH" wrote in message
...
I have a series of Workbooks (20+) each contain very detailed worksheets
(~42). I need to provide a summary in one workbook. Each workbook was sort
of a template, so the file names, tabs and calculations are all common. Each
workbook is in a separate directory. I am trying to sum all 20 sheets in one
sheet, but I run into a problem with the formula being to long.

ie c:\dir1\filename.xls![input tab] E$1$+c:\dir2\filename.xls![input tab]
E$1$ .
How do I create a name to shorten the link that can be copied throughout
the workbook?
=name[input tab]$E$1+name2[input tab]$E$1. I have tried to setup a
separate sheet with the root of each file in a cell and then try to combine
this cell with the rest formula with no luck. Any suggestions?

Thanks Andy
Sorry for the long note, but I cannot copy all the sheets into the same workbook to perform this type of rollup.

What I am trying to do is a similar rollup, but with multiple workbooks. Ie in your sheet, North, West, East would all be labeled a sheet "data" in different workbooks and Each workbook is named the same "region". There are separate directories, North, West, East that all contains this workbook "Region" containing the sheet "data".

Any thoughts?



"Andy Wiggins" wrote:

If I've understood you, the summary you want means adding up all the detail
sheets. If that's the case then this file might be a help:
http://www.bygsoftware.com/examples/zipfiles/consol.zip
It's in the "Accountants" section on page:
http://www.bygsoftware.com/examples/examples.htm

The "Bread-Roll" consolidation method - great for accountants. See how
simple it is to consolidate any combination of your organisation's accounts.
(No VBA used)

--
Regards
Andy Wiggins
www.BygSoftware.com
Home of "Save and BackUp",
"The Excel Auditor" and "Byg Tools for VBA"



"MJH" wrote in message
...
I have a series of Workbooks (20+) each contain very detailed worksheets
(~42). I need to provide a summary in one workbook. Each workbook was sort
of a template, so the file names, tabs and calculations are all common. Each
workbook is in a separate directory. I am trying to sum all 20 sheets in one
sheet, but I run into a problem with the formula being to long.

ie c:\dir1\filename.xls![input tab] E$1$+c:\dir2\filename.xls![input tab]
E$1$ .
How do I create a name to shorten the link that can be copied throughout
the workbook?
=name[input tab]$E$1+name2[input tab]$E$1. I have tried to setup a
separate sheet with the root of each file in a cell and then try to combine
this cell with the rest formula with no luck. Any suggestions?

A regular client of mine does something similar to what you want based on
the Bread-Roll method.
Some of their cost centre managers will file link their workbooks between
Bread and Roll whilst others will paste-values.
If the files are on a local drive and not on a server, file linking might
work. Otherwise I usually recoment using paste-values as this gives the user
positive control over what figures are used rather than guessing as to what
state source workbooks are in.

--
Regards
Andy Wiggins
www.BygSoftware.com
Home of "Save and BackUp",
"The Excel Auditor" and "Byg Tools for VBA"



"MJH" wrote in message
...
Thanks Andy
Sorry for the long note, but I cannot copy all the sheets into the same
workbook to perform this type of rollup.

What I am trying to do is a similar rollup, but with multiple workbooks.
Ie in your sheet, North, West, East would all be labeled a sheet "data" in
different workbooks and Each workbook is named the same "region". There are
separate directories, North, West, East that all contains this workbook
"Region" containing the sheet "data".

Any thoughts?



"Andy Wiggins" wrote:

If I've understood you, the summary you want means adding up all the
detail
sheets. If that's the case then this file might be a help:
http://www.bygsoftware.com/examples/zipfiles/consol.zip
It's in the "Accountants" section on page:
http://www.bygsoftware.com/examples/examples.htm

The "Bread-Roll" consolidation method - great for accountants. See how
simple it is to consolidate any combination of your organisation's
accounts.
(No VBA used)

--
Regards
Andy Wiggins
www.BygSoftware.com
Home of "Save and BackUp",
"The Excel Auditor" and "Byg Tools for VBA"



"MJH" wrote in message
...
I have a series of Workbooks (20+) each contain very detailed
worksheets
(~42). I need to provide a summary in one workbook. Each workbook was
sort
of a template, so the file names, tabs and calculations are all common.
Each
workbook is in a separate directory. I am trying to sum all 20 sheets in
one
sheet, but I run into a problem with the formula being to long.

ie c:\dir1\filename.xls![input tab] E$1$+c:\dir2\filename.xls![input
tab]
E$1$ .
How do I create a name to shorten the link that can be copied
throughout
the workbook?
=name[input tab]$E$1+name2[input tab]$E$1. I have tried to setup a
separate sheet with the root of each file in a cell and then try to
combine
this cell with the rest formula with no luck. Any suggestions?

Thanks again.
The linking doesn't work as the formula is too long, and the cut paste values starts to get a bit unmannagable with 42 worksheets in each work book.

Is there anyway to either create a shortened name for the link? ie define the root of the workbook directory as a variable, so that I can have a shorten formula.
say that C:\data\region\North\data.xls is defined as "MMM", then join this with the sheet and cell reference in the file,
MMM&[Data]!C1+NNN&[Data]!C1+etc?
I thought in the old xl we could use Textref as a way to join text in part of a formula, but cannot seem to get a cell which I defined as MMM with the root directory in it to combine as part of the formula.
Any thoughts?

"MJH" wrote:

I have a series of Workbooks (20+) each contain very detailed worksheets (~42). I need to provide a summary in one workbook. Each workbook was sort of a template, so the file names, tabs and calculations are all common. Each workbook is in a separate directory. I am trying to sum all 20 sheets in one sheet, but I run into a problem with the formula being to long.

ie c:\dir1\filename.xls![input tab] E$1$+c:\dir2\filename.xls![input tab] E$1$ .
How do I create a name to shorten the link that can be copied throughout the workbook?
=name[input tab]$E$1+name2[input tab]$E$1. I have tried to setup a separate sheet with the root of each file in a cell and then try to combine this cell with the rest formula with no luck. Any suggestions?