Shorten Links in Formulas

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

MJH - 19 Jun 2004 17:54 GMT

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?

Reply to this Message

Andy Wiggins - 20 Jun 2004 09:12 GMT

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)

Signature

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

> 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?

Reply to this Message

MJH - 23 Jun 2004 11:31 GMT

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?

> 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?

Reply to this Message