formula is too long

[Guest]

I am trying to link to several workbooks elsewhere on my computer, each book
has one sheet per month and a total sheet, the sheet i am working on will
contain all the same sheets but i want it to add up each month from the other
sheets so i get a total for each month and a total of everything all together.
When i try to put the formula in i get an error saying 'the formula is too
long' Is there any other way of linking to them, or making the formula
allowed larger

 

[Pete_UK]

Post a copy of your formula, then someone might be able to suggest
improvements or ways around the limitations.

Pete

 

[Guest]

thanks Pete_uk
this is the type of thing i am trying to get around
=SUM('S:\rn\Information\Services EAST and WEST 06-07\Community\District
Nurses\carlisle\wigton\[wigton ALL.xls]MAR'!D29,'S:\rn\Information\Services
EAST and WEST 06-07\Community\District Nurses\carlisle\Fusehill St\[FHS
All.xls]MAR'!D32,'S:\Lin Kendal\Information\Services EAST and WEST
06-07\Community\District Nurses\carlisle\em\[EMG
ALL.xls]MAR'!D32,'S:\rn\Information\Services EAST and WEST
06-07\Community\District Nurses\carlisle\cue doc etc etc etc... It does the
same thing over and over again connecting to a lot of different workbooks

I hope you can help

 

[Dave Peterson]

If you're putting all the links in one cell (as a giant formula), how about just
adding another worksheet and put one link per cell (say A1:A9999)

Then put:
=sum(a:a) (or whatever your formula does)
in B1
and use =sheet99!b1
in the original's spot.

(and hide the worksheet if you want.)

 

[Roger Govier]

Hi

If I were on a network I think I would map
S:\rn\Information\Services EAST and WEST 06-07\Community\District
Nurses\carlisle\
to a drive letter e.g. Q:

Even if you are not networked then the good old DOS command SUBST is
still alive and well.
At the C:\> command prompt type
SUBST Q: S:\rn\Information\Services EAST and WEST
06-07\Community\District Nurses\carlisle\


Then just use Q: in your formula as
SUM(Q:\wigton\[wigton ALL.xls]MAR'!D29 etc.

Or create a folder off the root of S: called S:\Me and move all the
files you want to that single folder then use
SUM(S:\Me\[wigton ALL.xls]MAR'!D29 etc.


--
Regards

Roger Govier

thanks Pete_uk
this is the type of thing i am trying to get around
=SUM('S:\rn\Information\Services EAST and WEST
06-07\Community\District
Nurses\carlisle\wigton\[wigton
ALL.xls]MAR'!D29,'S:\rn\Information\Services
EAST and WEST 06-07\Community\District Nurses\carlisle\Fusehill
St\[FHS
All.xls]MAR'!D32,'S:\Lin Kendal\Information\Services EAST and WEST
06-07\Community\District Nurses\carlisle\em\[EMG
ALL.xls]MAR'!D32,'S:\rn\Information\Services EAST and WEST
06-07\Community\District Nurses\carlisle\cue doc etc etc etc... It
does the
same thing over and over again connecting to a lot of different
workbooks

I hope you can help

Post a copy of your formula, then someone might be able to suggest
improvements or ways around the limitations.

Pete