Forumula too long

[kippers]

Hi,

I am currently working on a number of spreadsheets each containing a number
of worksheets. I have created one 'consolidated' spreadsheet in which I have
added up all the data from all spreadsheets so that it shows overall totals.
This works fine. However, as I am doing some work at home and some at work,
whenever I copy and paste the files from one location to another the formulas
in the consolidated spreadsheet become too long for Excel to handle. This is
because the formula includes the full path name of the spreadsheet rather
than just the spreadsheet name. This happens every time I move the
spreadsheets from my C: drive at home to my network drive at work.

Does anyone know if there is any way around this problem as I'm currently
having to re-enter all forumlas whenever I transfer work which is becoming
far too time consuming!

Any help gratefully appreciated.

R

 

[Jacob Skaria]

For open workbooks referring the name is enough.

Workbooks("Book1").sheets("Sheet1").Range

If this post helps click Yes

 

[Excelerate-nl]

I do not understand how are able to copy all the files on the server in your
office and then get the correct links in your totals sheet. Normally they
would refer to the files on your C-drive. Or do you open up all the sub-files
first before opening the totals files, which would normally work as links
will first look for opened files with the correct name? I doubt if long names
are the cause of Excel to fail.
If long formulas are the problem, why don't you limit the length of the
formula by splitting it in sub formula's or already have preprocessed
subtotals in the attached sheets?
Why don't you put all sheets in 1 workbook?

Instead of using the C drive or some dir in My documents, you might create a
network drive that you can can use off-line and synchronise when on line. In
this case your links will not change.

JB

 

[Shane Devenshire]

Hi,

1. If the file with the links and the file that you are linked to are both
open then you can use INDIRECT - however, this function does not work with
closed workbook.

2. You can decease the formula size by using range names, for example if

='F:\Classes\Pivot Tables - Class Files\[Lesson05.xls]Testing Refresh'!$C$2

was your original reference and you named C2 T then you could reduce the
formula to

='F:\Classes\Pivot Tables - Class Files\Lesson05.xls'!T

3. Excel 2007 support longer formulas in cell about 8 times as long.

4. You can break up formula references into multiple cells, for example
='F:\Classes\Pivot Tables - Class Files\Lesson05.xls'!T+'F:\Classes\Pivot
Tables - Class Files\Lesson05.xls'!M
could be broken into
='F:\Classes\Pivot Tables - Class Files\Lesson05.xls'!T
='F:\Classes\Pivot Tables - Class Files\Lesson05.xls'!M
and then in another cell =A1+A2

5. You might show us one of the formulas so we could add additional comments.