Error: The text string you entered is too long.
 

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

Error: The text string you entered is too long.
 

I have had a little luck naming the spreadsheet tab name the same name as the
workbook (works if you only have data on one tab). If you repost the
formula replacing all the link files with "file 1, file 2, ...", it would be
a little easier to follow.

"GTVT06" wrote:

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

Error: The text string you entered is too long.
 

GTVT06 wrote...
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?
....

(reformatted replacing your pathname with [PN])
=SUM(SUMIF('[PN]Spreadsheet'!$A$5:$A$35,"<="&LOOKUP(TODAY(),KC!B31 :E31),
'[PN]Spreadsheet'!$D$5:$D$35)-SUMIF('[PN]Spreadsheet'!$A$5:$A$35,
"<="&LOOKUP(TODAY()-7,KC!B31:E31),'[PN]Spreadsheet'!$D$5:$D$35))
/(SUMIF('[PN]Spreadsheet'!$A$5:$A$35,"<="&LOOKUP(TODAY(),KC!B31 :E31),
'[PN]Spreadsheet'!$C$5:$C$35)-SUMIF('[PN]Spreadsheet'!$A$5:$A$35,
"<="&LOOKUP(TODAY()-7,KC!B31:E31),'[PN]Spreadsheet'!$C$5:$C$35))

The length of the pathname isn't the only problem. Using single quotes,
' , in Excel workbook filenames is a bad idea because they need to be
doubled when used in formulas because Excel also uses single quotes to
enclose workbook/worksheet names that contain spaces. This was
exceptionally shortsighted on the part of the Excel developer(s) who
did this, but we're stuck with it.

Next, even if your formula weren't too long, SUMIF would evaluate to
#REF! errors if the workbook you're refering to were closed. That's
because SUMIF only accepts as its 1st and 3rd arguments what Excel
considers ranges, and as far as Excel is concerned, ranges only exist
in OPEN files.

So if you intend to use this formula when the file is closed, you can't
use SUMIF.

Eliminate the useless use of SUM and the formula becomes

=(SUMIF('[PN]Spreadsheet'!$A$5:$A$35,"<="&LOOKUP(TODAY(),KC!B31 :E31),
'[PN]Spreadsheet'!$D$5:$D$35)-SUMIF('[PN]Spreadsheet'!$A$5:$A$35,
"<="&LOOKUP(TODAY()-7,KC!B31:E31),'[PN]Spreadsheet'!$D$5:$D$35))
/(SUMIF('[PN]Spreadsheet'!$A$5:$A$35,"<="&LOOKUP(TODAY(),KC!B31 :E31),
'[PN]Spreadsheet'!$C$5:$C$35)-SUMIF('[PN]Spreadsheet'!$A$5:$A$35,
"<="&LOOKUP(TODAY()-7,KC!B31:E31),'[PN]Spreadsheet'!$C$5:$C$35))

Both numerator and both denominator terms use similar conditions,

'[PN]Spreadsheet'!$A$5:$A$35<=LOOKUP(TODAY(),KC!B31:E31 )

and

'[PN]Spreadsheet'!$A$5:$A$35<=LOOKUP(TODAY()-7,KC!B31:E31)

Both could be rewritten together as

'[PN]Spreadsheet'!$A$5:$A$35<=LOOKUP(TODAY()-{0,7},KC!B31:E31)

Then reflect that the 1st indicates addition and the 2nd subtraction.

('[PN]Spreadsheet'!$A$5:$A$35<=LOOKUP(TODAY()-{0,7},KC!B31:E31))*{1,-1}

This evaluates to a 31 row by 2 column array of +1s and 0s in the 1st
column and -1s and 0s in the second column. Multiply them rowwise
against [PN]Spreadsheet's columns D and C and sum the respective
results to get the numerator and denominator terms. Easiest to do that
using MMULT.

=SUM(MMULT(TRANSPOSE(('[PN]Spreadsheet'!$A$5:$A$35
<=LOOKUP(TODAY()-{0,7},KC!B31:E31))*{1,-1}),'[PN]Spreadsheet'!$D$5:$D$35))
/SUM(MMULT(TRANSPOSE(('[PN]Spreadsheet'!$A$5:$A$35
<=LOOKUP(TODAY()-{0,7},KC!B31:E31))*{1,-1}),'[PN]Spreadsheet'!$C$5:$C$35))

Now replace the [PN] token with the full pathname.

=SUM(MMULT(TRANSPOSE(('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()-{0,7},KC!B31:E31))*{1,-1}),
'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))
/SUM(MMULT(TRANSPOSE(('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()-{0,7},KC!B31:E31))*{1,-1}),
'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))

Remove the newlines and this is spans a mere 741 chars. It's also needs
to be entered as an array formula.