Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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)) |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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)) |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Display names | Excel Discussion (Misc queries) | |||
Browse File for Mac | Excel Discussion (Misc queries) | |||
More than 3 conditional formats? | Excel Discussion (Misc queries) | |||
can you find specific text in a string ignoring any other text | Excel Discussion (Misc queries) | |||
Formulas dealing with text data | Excel Worksheet Functions |