Formula Too Long

[Todd]

Is there anyway to expand the amount of characters that a
formula will allow? I am trying to link 14 different
spreadsheets to one and I keep getting the formula is too
long error. Any help will be appreciated. Thanks in
advance.

Todd

 

[Don Guillett]

try creating a name for the link
then =Joe*bill

 

[Ken Wright]

Give us an example of the formula.

 

[Todd]

The formula is a basic sum formulua but the files are save
in different folders which makes the formula really long
when they are not opened. For example the formula would
be =sum(G:\NAS\Qtr1\Jan\E W.xls This path would be in the
formula 14 different times with the.xls name changing each
time.

 

[Ken Wright]

OK, so use Don's suggestion.

Create one of the links in a cell all by itself, and then close the workbook you are linking to,
so that you get the full directory in the cell. Now copy the link in it's entirety, and then do
Insert / Name / Define, put Link1 in the 'Names in Workbook' box and then paste the link into the
'refers to' box and hit OK.

Now in any cell, just put =Link1 and you will see your data.

Do this for all 14 links and then you can do =Link1+Link2+Link3 etc

 

[Dave Peterson]

Maybe you could shorten the path (move the files) and choose shorter folder
names.

But I think I might use 14 different cells (on a hidden worksheet????) and just
write a formula like =sum(ThatHelperRangeOnThatHiddenSheet)

(it might be easier to update when you add the 15th, 16th....