Error: The text string you entered is too long.

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))
 
G

Guest

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...
 
G

GTVT06

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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Error: The text string you entered is too long. 1
Get names of closed workbooks? 5
Error 0xC00D3E8E: The property is read only. One way to fix. 1
Excel 2003 - VBA - Indirect Addressing 1
Help with type mismatch error 4
Hope you can help - Tried the IE newsgroup... 1
lookup latest entered match 7
Compile error - Expected: list seperator or ) 4

Top