formula is too long

M

monkey1

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
 
P

Pete_UK

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

Pete
 
M

monkey1

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
 
D

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.)
 
R

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


monkey1 said:
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

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

Pete
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top