I have several spreadsheets linked to a main one. Today when updating the
source data, I get the error message "formula too long". (The new source
document link has exactly the same number of characters as before.)

This has never happened before - there are not 1024 characters in the link
so I do not understand why it has happened.

I have searched this and the error user groups but cannot find anything that
relates to this error message being associated with links.

You assistance is greatly appreciated.

I don't know why this would suddenly start happening, unless you had
changed the link source using Edit Links and the resultant formula
became too long when including the full path to the source file.

The 1024 limit is not on the length of the linked file name itself but
on any formula incorporating that name.

If, for example you had a formula
=IF('C:\MyDir\[MyBook.xls]Sheet1'!A1=0,"",'C:\MyDir\[MyBook.xls]Sheet1'
!A1)
and you used Edit Links to change the source to some long network
path then the formula length would increase by twice the change in path
length.

Can you identify a formula that is giving the trouble?
If so, please post it here in case it triggers some other thoughts.


Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

Bill

I am actually trying to update the link via edit links.

When I go in to edit links, select the relevant spreadsheet and go through
the various server areas to find the and select the correct file I then get
the error message and have to Ctrl Alt Del and End Task in Task Manager as
Excel hangs.

The previous link was to a sheet on the server in a in a folder called 2007
then subfolder called Q2_07, the revised spreadsheet sits on exactly the same
2007 folder and a new subfolder called Q3_07 with the name of the file also
changed to Q3_07.xls - so the full path name is no longer than before.

Regards

"Bill Manville" wrote:

I don't know why this would suddenly start happening, unless you had
changed the link source using Edit Links and the resultant formula
became too long when including the full path to the source file.

The 1024 limit is not on the length of the linked file name itself but
on any formula incorporating that name.

If, for example you had a formula
=IF('C:\MyDir\[MyBook.xls]Sheet1'!A1=0,"",'C:\MyDir\[MyBook.xls]Sheet1'
!A1)
and you used Edit Links to change the source to some long network
path then the formula length would increase by twice the change in path
length.

Can you identify a formula that is giving the trouble?
If so, please post it here in case it triggers some other thoughts.


Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

That is strange.
Does it help if you have the Q3_07.xls file open before doing the
Change Source operation?

Which version of Excel?
Can you post an example formula (with the source workbook closed)? -
the longest one you can think of

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup