Formula is Too Long

Hi,

I published my question to the Application Error NG, but I'm not sure which one is the most suitable between the two NGs, therofore, given that I did not get an answer yet, I publish my question here as well.

Let's describe the problem with an example:

FOLDER A
10 Excel WBs with formulas
1 Excel WB that consolidate the just said WBs

In the 10 WBs I had to delete the values in a column in order to get a different analysis, therefore I took the following steps:

I did create a
FOLDER B
I copied all the content of Folder A and worked on the 10 WBs to get the numbers needed.
Then, I opened the consolidated WB, it asked me if I wanted to update but I realised that the path was still the one to FOLDER A, therefore I clicked NO and went to Edit/Links in order to change the source.
One by one I highlighted the path to the new WBs, untill WB no 5 it did his job, change the path to FOLDER B and update, from no 6 onwards the error "Formula Is Too Long" poped up.
I tried to change the order of updating to check if this was depending from a specific file, but nothing changed, any path untill WB 5 it works, then it gives the error.
The funny thing is that clicking the OK on the error window for around 30 times, triggers the process to start and create the right path, but also the old one remains there, therefore I find myself with two paths starting from WB5.

Something like:

H:\FOLDER B\WB1 OK
H:\FOLDER B\WB2 OK
H:\FOLDER B\WB3 OK
H:\FOLDER B\WB4 OK
H:\FOLDER B\WB5 OK
H:\FOLDER A\WB6 OK
H:\FOLDER B\WB6 OK
H:\FOLDER A\WB7 OK
H:\FOLDER B\WB7 OK

Etc. till WB10

The data look fine but I noticed strange things in the charts, therefore I'd like to re-do the process again.

How can I solve this issue? Is this a bug?

Why once I copied the files from FOLDER A to FOLDER B the path did not change automatically ? This would have made things easier.

I hope I've been clear enough and that you can help.

Thank you
Alex



Expand AllCollapse All

Metallo wrote:
One by one I highlighted the path to the new WBs, untill WB no 5 it did his job, change the path to FOLDER B and update, from no 6 onwards the error "Formula Is Too Long" poped up.
I tried to change the order of updating to check if this was depending from a specific file, but nothing changed, any path untill WB 5 it works, then it gives the error.

Unfortunately you have hit a limitation in Excel.
The maximum length of a formula is 1024 characters.
Where external links are involved that includes the path to the file concerned.

I guess your FOLDER B had a longer path than FOLDER A.

Ways around it:
- move the files to a location with a shorter path
- if FOLDER B is on a network drive, in Windows Explorer map a drive letter to FOLDER B (or its parent)
and use that drive letter in the link.
- break up the formula so that you have one cell containing part of the result (say from 5 of the
other workbooks) and a second cell containing the data from the other 5, then add the results together.

I think that Microsoft are aware of the problems this can cause so maybe one day there
will be a version in which it is not a problem. But for now we have to live with it.

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

Bill,

I just made the folder name shorter and the error doesn't pop up.
Although, it still does strange things before updating, when I get to click on the file to be updated, instead of clicking OK I have to click cancel several times, then it starts to process.
Apparently the numbers are right, which is important, but is there a way to get rid of the old invalid links into the Edit/Links window? I cannot use break links otherwise the links disappear.

Thank you
Alex

"Bill Manville" wrote:

Metallo wrote:
One by one I highlighted the path to the new WBs, untill WB no 5 it did his job, change the path to FOLDER B and update, from no 6 onwards the error "Formula Is Too Long" poped up.
I tried to change the order of updating to check if this was depending from a specific file, but nothing changed, any path untill WB 5 it works, then it gives the error.

Unfortunately you have hit a limitation in Excel.
The maximum length of a formula is 1024 characters.
Where external links are involved that includes the path to the file concerned.

I guess your FOLDER B had a longer path than FOLDER A.

Ways around it:
- move the files to a location with a shorter path
- if FOLDER B is on a network drive, in Windows Explorer map a drive letter to FOLDER B (or its parent)
and use that drive letter in the link.
- break up the formula so that you have one cell containing part of the result (say from 5 of the
other workbooks) and a second cell containing the data from the other 5, then add the results together.

I think that Microsoft are aware of the problems this can cause so maybe one day there
will be a version in which it is not a problem. But for now we have to live with it.

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

Bill,

I found the way to delete the old links.
Once created the new ones, I do the same process with the olbsolete links and since the system will end up to have two links which are the same, automatically will eliminate one of the two.

Regards
Alex

"Metallo" wrote:

Bill,

I just made the folder name shorter and the error doesn't pop up.
Although, it still does strange things before updating, when I get to click on the file to be updated, instead of clicking OK I have to click cancel several times, then it starts to process.
Apparently the numbers are right, which is important, but is there a way to get rid of the old invalid links into the Edit/Links window? I cannot use break links otherwise the links disappear.

Thank you
Alex

"Bill Manville" wrote:

Metallo wrote:
One by one I highlighted the path to the new WBs, untill WB no 5 it did his job, change the path to FOLDER B and update, from no 6 onwards the error "Formula Is Too Long" poped up.
I tried to change the order of updating to check if this was depending from a specific file, but nothing changed, any path untill WB 5 it works, then it gives the error.

Unfortunately you have hit a limitation in Excel.
The maximum length of a formula is 1024 characters.
Where external links are involved that includes the path to the file concerned.

I guess your FOLDER B had a longer path than FOLDER A.

Ways around it:
- move the files to a location with a shorter path
- if FOLDER B is on a network drive, in Windows Explorer map a drive letter to FOLDER B (or its parent)
and use that drive letter in the link.
- break up the formula so that you have one cell containing part of the result (say from 5 of the
other workbooks) and a second cell containing the data from the other 5, then add the results together.

I think that Microsoft are aware of the problems this can cause so maybe one day there
will be a version in which it is not a problem. But for now we have to live with it.

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