Excel Links

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Pat I - 23 Jan 2008 02:11 GMT

I am using Excel 2000 and have several workbook that link to a spreadsheet.
Within each workbook, there are links to several cells in the spreadsheet
(which is a different file). I cannot link all of the applicable cells from
the spreadsheet into the appropriate workbook. In some cells, I get the
error "formula too long". What does this mean and how can I fix it so that I
can link to all the applicable cells from the spreadsheet?

Reply to this Message

Bill Manville - 23 Jan 2008 23:08 GMT

If you have a formula like =[OtherBook.xls]SheetName!RangeName
and Otherbook.xls resides at C:\MyFolder
the formula (when OtherBook.xls is closed) is
='C:\MyFolder\[OtherBook.xls]SheetName'!RangeName

The limit on the length of a formula is 1024 characters.
The formula above would count as 48 characters (even if OtherBook.xls
is open and it takes the shorter form when viewed in the formula bar)

If OtherBook.xls is in a directory with a very long path, has a long
workbook name, a long sheet name and/or a long range name you might hit
the limit, particularly if the formula contains multiple references to
OtherBook -
e.g. if the formula is
=IF([OtherBook.xls]SheetName!RangeName=0,"",[OtherBook.xls]SheetName!Ra
ngeName)

then the full form of the link (when Otherbook.xls closed) would only
have to be about 500 characters to break the limit.

Hope this helps.
If not, give us an example of the formulas you are trying to create
with detail of where the Otherbook.xls is located.

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

Reply to this Message

Pat I - 31 Jan 2008 03:30 GMT

Thanks for replying Bill.
Sorry it took me so long to get back with you, but I couldn't find the
discussion group and the link in the notification email didn't work. I'll
save the page to my favorites this time. :)

The cells do contain the file path, folder name, subfolder name, workbook
name, sheet name and cell range. Would it help if I move both files into the
same folder? Can I manually delete the file path from the cell? Here is an
example of the cell info that is linked to the spreadsheet:

='C:\Documents and Settings\Owner\My
Documents\FolderName\SubfolderName\[FileName.xls]SheetName!CellName

Please note that this sheet was originally created on my Windows XP
computer, but I now have it on my Windows Vista computer.

Signature

Thank you for your reply,

> If you have a formula like =[OtherBook.xls]SheetName!RangeName
> and Otherbook.xls resides at C:\MyFolder
[quoted text clipped - 23 lines]
> MVP - Microsoft Excel, Oxford, England
> No email replies please - respond to newsgroup

Reply to this Message

Bill Manville - 31 Jan 2008 07:24 GMT

> ='C:\Documents and Settings\Owner\My
> Documents\FolderName\SubfolderName\[FileName.xls]SheetName!CellName

That, in itself doesn't indicate how close to the 1024 limit you are
sailing - depends on the actual names.

If any of the names is particularly long it would be wise to shorten it
by renaming.

Moving files into the same folder would not in itself help unless the
full path to the folder was shorter.

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

Reply to this Message