Sum Function

  • Thread starter Thread starter aris
  • Start date Start date
A

aris

I am creating a summary worksheet. I am using the SUM
function to add up values from numerous other excel
worksheets. These worksheets are located on our network
server.
I select the cell from each worksheet that I want to
include in the SUM function. All seems just fine at this
point. However after I save the summary worksheet and open
it up again I can see the SUM function now references each
cell where it is physically located on the network. For
example, worksheet
fundy.xls!C7 now becomes
G:\strategy plans and evaluation\business
innovations\national pass\orders\2004\fundy.xls!C7.
As a result, it seems when the sum function reaches about
1000 characters in total, it has a hernia and truncates
off the rest of the original sum function.
Is this a limitation in excel? Any suggestions to get
around this?
Thanks.
 
Yes there is a limitation, 1024 characters for a formula, 32567 for text
so your observation seems to be correct, there are ways to overcome this,
post your formula
 
aris said:
I am creating a summary worksheet. I am using the SUM
function to add up values from numerous other excel
worksheets. These worksheets are located on our network
server.
I select the cell from each worksheet that I want to
include in the SUM function. All seems just fine at this
point. However after I save the summary worksheet and open
it up again I can see the SUM function now references each
cell where it is physically located on the network. For
example, worksheet
fundy.xls!C7 now becomes
G:\strategy plans and evaluation\business
innovations\national pass\orders\2004\fundy.xls!C7.
As a result, it seems when the sum function reaches about
1000 characters in total, it has a hernia and truncates
off the rest of the original sum function.
Is this a limitation in excel? Any suggestions to get
around this?
Thanks.
Click to expand...

Formulas are limited to 1024 characters. See "specifications for Microsoft
Excel" in Help.
The link reference changes when you close the source workbook; how else
would Excel know where to get the data?
You could put each link in a separate cell (1024 characters is surely
sufficient for one external reference) and then sum these local cells.
 
Thanks very much for confirming my suspicion.
I was thinking there might be some way of directing the
SUM function to the same directory since all excel
worksheets are coming from one directory.
Anyways, this is how the current SUM function currently
looks:
=SUM('G:\Strategic Planning and Evaluation\Business
Innovations\National Pass\2004-05\Orders\[Dawson.xls]
English'!C7,'G:\Strategic Planning and Evaluation\Business
Innovations\National Pass\2004-05\Orders\[Elk Island.xls]
English'!C7,'G:\Strategic Planning and Evaluation\Business
Innovations\National Pass\2004-05\Orders\[FOL.xls]English'!
C7,'G:\Strategic Planning and Evaluation\Business
Innovations\National Pass\2004-05\Orders\[Fort Chambly.xls]
French'!C7,'G:\Strategic Planning and Evaluation\Business
Innovations\National Pass\2004-05\Orders\[Fort George.xls]
English'!C7,'G:\Strategic Planning and Evaluation\Business
Innovations\National Pass\2004-05\Orders\[Fort Henry.xls]
English'!C7,'G:\Strategic Planning and Evaluation\Business
Innovations\National Pass\2004-05\Orders\[Fort Langley.xls]
English'!C7)
 
Yikes!

Aris,

one way would be to link to all different workbooks in the summary sheet and
the sum that range,
assume that you in IV1 put

='G:\Strategic Planning and Evaluation\BusinessInnovations\National
Pass\2004-05\Orders\[Dawson.xls]
English'!C7

in IV2 you put

='G:\Strategic Planning and Evaluation\BusinessInnovations\National
Pass\2004-05\Orders\[Elk Island.xls]English'!C7

and so on, if you open the other workbooks then you can just type in the
equal sign and click in C7 and press enter for each workbook and Excel will
create the path.
Once you are done you can just use

=SUM(IV1:IV50)

or whatever the range might be..



--

Regards,

Peo Sjoblom


aris said:
Thanks very much for confirming my suspicion.
I was thinking there might be some way of directing the
SUM function to the same directory since all excel
worksheets are coming from one directory.
Anyways, this is how the current SUM function currently
looks:
=SUM('G:\Strategic Planning and Evaluation\Business
Innovations\National Pass\2004-05\Orders\[Dawson.xls]
English'!C7,'G:\Strategic Planning and Evaluation\Business
Innovations\National Pass\2004-05\Orders\[Elk Island.xls]
English'!C7,'G:\Strategic Planning and Evaluation\Business
Innovations\National Pass\2004-05\Orders\[FOL.xls]English'!
C7,'G:\Strategic Planning and Evaluation\Business
Innovations\National Pass\2004-05\Orders\[Fort Chambly.xls]
French'!C7,'G:\Strategic Planning and Evaluation\Business
Innovations\National Pass\2004-05\Orders\[Fort George.xls]
English'!C7,'G:\Strategic Planning and Evaluation\Business
Innovations\National Pass\2004-05\Orders\[Fort Henry.xls]
English'!C7,'G:\Strategic Planning and Evaluation\Business
Innovations\National Pass\2004-05\Orders\[Fort Langley.xls]
English'!C7)
-----Original Message-----
Yes there is a limitation, 1024 characters for a formula, 32567 for text
so your observation seems to be correct, there are ways to overcome this,
post your formula

--

Regards,

Peo Sjoblom





.
Click to expand...
Click to expand...
 
Back
Top