Formula too Long

[Chuck]

I have the following formula in one of my excel cell and I would like
to link other files to this formula but I get a message saying "formula
is too long." What can I do to work around that? Please advise!
P.S. I would like to do this e/o all necessary files open if possible.

=+'W:\IB EBS\2006-08-04 P\NII\Revised NII Files\Legacy\[TL NII Proj
2006-07-25.xls]iEC Adjustments'!C5+'W:\IB EBS\2006-08-04 P\NII\Revised
NII Files\Legacy\[Nelico NII Proj 2006-07-25.xls]iEC
Adjustments'!C5+'W:\IB EBS\2006-08-04 P\NII\Revised NII
Files\Legacy\[MLIG NII Proj 2006-07-25.xls]iEC Adjustments'!C5+'W:\IB
EBS\2006-08-04 P\NII\Revised NII Files\Legacy\[MLFS+MMA NII Proj
2006-07-25.xls]iEC Adjustments'!C5+'W:\IB EBS\2006-08-04 P\NII\Revised
NII Files\Legacy\[GenAm NII Proj 2006-07-25.xls]iEC
Adjustments'!C5+'W:\IB EBS\2006-08-04 P\NII\Revised NII
Files\Legacy\[MoRe NII Proj 2006-07-25.xls]iEC Adjustments'!C5+'W:\IB
EBS\2006-08-04 P\NII\Revised NII Files\Legacy\[Exeter NII Proj
2006-07-25.xls]iEC Adjustments'!C5+'[CLIC NII Proj 2006-07-25.xls]iEC
Adjustments'!$C$5+'[FLIC NII Proj 2006-07-25.xls]iEC
Adjustments'!$C$5+'[MVATIC NII Proj 2006-07-25.xls]iEC
Adjustments'!$C$5+'[TIC NII Proj 2006-07-25.xls]iEC
Adjustments'!$C$5+'[MVATLAC NII Proj 2006-07-25.xls]iEC
Adjustments'!$C$5

 

[Niek Otten]

Hi Chuck,

Why not have a table with simple formulas like

='W:\IB EBS\2006-08-04 P\NII\Revised NII Files\Legacy\[TL NII Proj 2006-07-25.xls]iEC Adjustments'!C5
='W:\IB EBS\2006-08-04 P\NII\Revised NII Files\Legacy\[Nelico NII Proj 2006-07-25.xls]iEC Adjustments'!C5

etc and one SUM formula to add all these?

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|I have the following formula in one of my excel cell and I would like
| to link other files to this formula but I get a message saying "formula
| is too long." What can I do to work around that? Please advise!
| P.S. I would like to do this e/o all necessary files open if possible.
|
| =+'W:\IB EBS\2006-08-04 P\NII\Revised NII Files\Legacy\[TL NII Proj
| 2006-07-25.xls]iEC Adjustments'!C5+'W:\IB EBS\2006-08-04 P\NII\Revised
| NII Files\Legacy\[Nelico NII Proj 2006-07-25.xls]iEC
| Adjustments'!C5+'W:\IB EBS\2006-08-04 P\NII\Revised NII
| Files\Legacy\[MLIG NII Proj 2006-07-25.xls]iEC Adjustments'!C5+'W:\IB
| EBS\2006-08-04 P\NII\Revised NII Files\Legacy\[MLFS+MMA NII Proj
| 2006-07-25.xls]iEC Adjustments'!C5+'W:\IB EBS\2006-08-04 P\NII\Revised
| NII Files\Legacy\[GenAm NII Proj 2006-07-25.xls]iEC
| Adjustments'!C5+'W:\IB EBS\2006-08-04 P\NII\Revised NII
| Files\Legacy\[MoRe NII Proj 2006-07-25.xls]iEC Adjustments'!C5+'W:\IB
| EBS\2006-08-04 P\NII\Revised NII Files\Legacy\[Exeter NII Proj
| 2006-07-25.xls]iEC Adjustments'!C5+'[CLIC NII Proj 2006-07-25.xls]iEC
| Adjustments'!$C$5+'[FLIC NII Proj 2006-07-25.xls]iEC
| Adjustments'!$C$5+'[MVATIC NII Proj 2006-07-25.xls]iEC
| Adjustments'!$C$5+'[TIC NII Proj 2006-07-25.xls]iEC
| Adjustments'!$C$5+'[MVATLAC NII Proj 2006-07-25.xls]iEC
| Adjustments'!$C$5
|

 

[Tom Ogilvy]

formula are limited to 1024 characters when expressed in R1C1 notation.

so you can physically move you directory closer to the root,
Rename directories, files and sheets with shorter names

or

You could created defined names (insert=>Name=>define) that refer to the
cells, then use those defined names in your formulas