Error: The text string you entered is too long.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

GTVT06 - 15 Jun 2006 12:57 GMT

Can someone help me shorten this formula, without having to change the
path location of the linked sheet in the formula or without having to
use additional cells in either sheet to do the calculation?
The result of the formula only shows up if I have the linked
spreadsheet open because it will shorten "('C:\Open folder\Dummy
folder\Default\Sub Folder\Another Folder\2nd to last folder\last folder
"06\[Name of spreadsheet ''06.xls]Spreadsheet'!" to just "[Name of
spreadsheet ''06.xls]Spreadsheet'!" Which shortens the formula enough
to not use up the max amount of characters, I'm thinking there might be
an easier way to calculate the math in the formula to shorten it, or a
way to not display the whole path of the file.

=SUM(SUMIF('C:\Open folder\Dummy folder\Default\Sub Folder\Another
Folder\2nd to last folder\last folder "06\[Name of spreadsheet
''06.xls]Spreadsheet'!$A$5:$A$35,"<="&LOOKUP(TODAY(),KC!B31:E31),'C:\Open
folder\Dummy folder\Default\Sub Folder\Another Folder\2nd to last
folder\last folder "06\[Name of spreadsheet
''06.xls]Spreadsheet'!$D$5:$D$35)-SUMIF('C:\Open folder\Dummy
folder\Default\Sub Folder\Another Folder\2nd to last folder\last folder
"06\[Name of spreadsheet
''06.xls]Spreadsheet'!$A$5:$A$35,"<="&LOOKUP(TODAY()-7,KC!B31:E31),'C:\Open
folder\Dummy folder\Default\Sub Folder\Another Folder\2nd to last
folder\last folder "06\[Name of spreadsheet
''06.xls]Spreadsheet'!$D$5:$D$35))/(SUMIF('C:\Open folder\Dummy
folder\Default\Sub Folder\Another Folder\2nd to last folder\last folder
"06\[Name of spreadsheet
''06.xls]Spreadsheet'!$A$5:$A$35,"<="&LOOKUP(TODAY(),KC!B31:E31),'C:\Open
folder\Dummy folder\Default\Sub Folder\Another Folder\2nd to last
folder\last folder "06\[Name of spreadsheet
''06.xls]Spreadsheet'!$C$5:$C$35)-SUMIF('C:\Open folder\Dummy
folder\Default\Sub Folder\Another Folder\2nd to last folder\last folder
"06\[Name of spreadsheet
''06.xls]Spreadsheet'!$A$5:$A$35,"<="&LOOKUP(TODAY()-7,KC!B31:E31),'C:\Open
folder\Dummy folder\Default\Sub Folder\Another Folder\2nd to last
folder\last folder "06\[Name of spreadsheet
''06.xls]Spreadsheet'!$C$5:$C$35))

Reply to this Message

CLR - 15 Jun 2006 18:23 GMT

You might try storing the "path" as a RangeName, like Insert > Name > Define

> type THEPATH in the NamesInWorkbook: window, and 'C:\Open

folder\Dummyfolder\Default\Sub Folder\Another Folder\2nd to last folder\last
folder"06\ in the RefersTo: window..........then use the RangeName THEPATH in
your formula in place of the actual string of path characters...........

hth
Vaya con Dios,
Chuck, CABGx3

> Can someone help me shorten this formula, without having to change the
> path location of the linked sheet in the formula or without having to
[quoted text clipped - 32 lines]
> folder\last folder "06\[Name of spreadsheet
> ''06.xls]Spreadsheet'!$C$5:$C$35))

Reply to this Message

GTVT06 - 15 Jun 2006 19:59 GMT

Thanks. But that don't appear to work. I'm getting a #VALUE! Error.

> You might try storing the "path" as a RangeName, like Insert > Name > Define
> > type THEPATH in the NamesInWorkbook: window, and 'C:\Open
[quoted text clipped - 42 lines]
> > folder\last folder "06\[Name of spreadsheet
> > ''06.xls]Spreadsheet'!$C$5:$C$35))

Reply to this Message