Error: The text string you entered is too long.

  • Thread starter Thread starter GTVT06
  • Start date Start date
G

GTVT06

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))
 
You might try storing the "path" as a RangeName, like Insert > Name > Define
type THEPATH in the NamesInWorkbook: window, and 'C:\Open
Click to expand...
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



GTVT06 said:
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))
Click to expand...
 
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
Click to expand...
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



GTVT06 said:
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))
Click to expand...
Click to expand...
 
Back
Top