If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Excel Links
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? |
#2
|
|||
|
|||
Excel Links
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 |
#3
|
|||
|
|||
Excel Links
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. -- Thank you for your reply, "Bill Manville" wrote: 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 |
#4
|
|||
|
|||
Excel Links
Pat I wrote:
='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 |
Thread Tools | |
Display Modes | |
|
|