| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Too Long error
The formula below works sort of ok, but I now need to change the formula to
reflect what will be Augusts results, I'm getting the error formula too long. Is there a way that this can be truncated at all? The formula is as follows - the sheet contains results broken down by team and individual in rows. If anyone can think of a better method for showing results, I'd be glad of that too! =SUM('I:\CCR\SAM\IPP\FCC Team B\July 2007\Emma Ward\[FCC Evaluation Tool - Team B - Emma Ward.xls]Sharron Dyke'!$F$32+'I:\CCR\SAM\IPP\FCC Team B\July 2007\Emma Ward\[FCC Evaluation Tool - Team B - Emma Ward.xls]Sharron Dyke'!$F$66+'I:\CCR\SAM\IPP\FCC Team B\July 2007\Emma Ward\[FCC Evaluation Tool - Team B - Emma Ward.xls]Sharron Dyke'!$F$100+'I:\CCR\SAM\IPP\FCC Team B\July 2007\Emma Ward\[FCC Evaluation Tool - Team B - Emma Ward.xls]Sharron Dyke'!$F$134+'I:\CCR\SAM\IPP\FCC Team B\July 2007\Emma Ward\[FCC Evaluation Tool - Team B - Emma Ward.xls]Sharron Dyke'!$F$168+'I:\CCR\SAM\IPP\FCC Team B\July 2007\Emma Ward\[FCC Evaluation Tool - Team B - Emma Ward.xls]Sharron Dyke'!$F$202+'I:\CCR\SAM\IPP\FCC Team B\July 2007\Emma Ward\[FCC Evaluation Tool - Team B - Emma Ward.xls]Sharron Dyke'!$F$236+'I:\CCR\SAM\IPP\FCC Team B\July 2007\Emma Ward\[FCC Evaluation Tool - Team B - Emma Ward.xls]Sharron Dyke'!$F$270) Regards, JDB |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula Too Long error
The first thing that occurs to me is that the longest thing in your formula
is the name of the Excel file. Shorten that and you have a lot of characters left over? I'm also thinking that possibly a SUMPRODUCT() formula may work, but I'd have to really dig into your formula, rip it apart and see what it's doing in the individual areas. And, if I recall correctly, SUMPRODUCT() may be one of those formulas that doesn't work across workbooks anyhow. But memory doesn't serve me well this morning, and I am pressed for time at the moment. I know you'd probably rather not change the name of the Excel file, but you might consider opening up both workbooks, then saving the one with the long file name under a new, shorter name. Then continue to do updates/data entry into the one with the long name, but just before opening this one to do data examination, copy the long file over the one with the short name, thus bringing it up to date. "JDB" wrote: The formula below works sort of ok, but I now need to change the formula to reflect what will be Augusts results, I'm getting the error formula too long. Is there a way that this can be truncated at all? The formula is as follows - the sheet contains results broken down by team and individual in rows. If anyone can think of a better method for showing results, I'd be glad of that too! =SUM('I:\CCR\SAM\IPP\FCC Team B\July 2007\Emma Ward\[FCC Evaluation Tool - Team B - Emma Ward.xls]Sharron Dyke'!$F$32+'I:\CCR\SAM\IPP\FCC Team B\July 2007\Emma Ward\[FCC Evaluation Tool - Team B - Emma Ward.xls]Sharron Dyke'!$F$66+'I:\CCR\SAM\IPP\FCC Team B\July 2007\Emma Ward\[FCC Evaluation Tool - Team B - Emma Ward.xls]Sharron Dyke'!$F$100+'I:\CCR\SAM\IPP\FCC Team B\July 2007\Emma Ward\[FCC Evaluation Tool - Team B - Emma Ward.xls]Sharron Dyke'!$F$134+'I:\CCR\SAM\IPP\FCC Team B\July 2007\Emma Ward\[FCC Evaluation Tool - Team B - Emma Ward.xls]Sharron Dyke'!$F$168+'I:\CCR\SAM\IPP\FCC Team B\July 2007\Emma Ward\[FCC Evaluation Tool - Team B - Emma Ward.xls]Sharron Dyke'!$F$202+'I:\CCR\SAM\IPP\FCC Team B\July 2007\Emma Ward\[FCC Evaluation Tool - Team B - Emma Ward.xls]Sharron Dyke'!$F$236+'I:\CCR\SAM\IPP\FCC Team B\July 2007\Emma Ward\[FCC Evaluation Tool - Team B - Emma Ward.xls]Sharron Dyke'!$F$270) Regards, JDB |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula Too Long error
Hi
Create a (hidden) sheet, with all links to other workbooks collected there as a table. You can now replace your formula with a simple one, which sums data from a range on this sheet. Btw, when you use addition (+) in your formula, then SUM is abundant (and vice versa). I.e. =SUM(Value1,Value2,...,ValueN) ,or =Value1+Value2+...+ValueN will do. -- Arvi Laanemets ( My real mail address: arvi.laanemets<attarkon.ee ) "JDB" wrote in message ... The formula below works sort of ok, but I now need to change the formula to reflect what will be Augusts results, I'm getting the error formula too long. Is there a way that this can be truncated at all? The formula is as follows - the sheet contains results broken down by team and individual in rows. If anyone can think of a better method for showing results, I'd be glad of that too! =SUM('I:\CCR\SAM\IPP\FCC Team B\July 2007\Emma Ward\[FCC Evaluation Tool - Team B - Emma Ward.xls]Sharron Dyke'!$F$32+'I:\CCR\SAM\IPP\FCC Team B\July 2007\Emma Ward\[FCC Evaluation Tool - Team B - Emma Ward.xls]Sharron Dyke'!$F$66+'I:\CCR\SAM\IPP\FCC Team B\July 2007\Emma Ward\[FCC Evaluation Tool - Team B - Emma Ward.xls]Sharron Dyke'!$F$100+'I:\CCR\SAM\IPP\FCC Team B\July 2007\Emma Ward\[FCC Evaluation Tool - Team B - Emma Ward.xls]Sharron Dyke'!$F$134+'I:\CCR\SAM\IPP\FCC Team B\July 2007\Emma Ward\[FCC Evaluation Tool - Team B - Emma Ward.xls]Sharron Dyke'!$F$168+'I:\CCR\SAM\IPP\FCC Team B\July 2007\Emma Ward\[FCC Evaluation Tool - Team B - Emma Ward.xls]Sharron Dyke'!$F$202+'I:\CCR\SAM\IPP\FCC Team B\July 2007\Emma Ward\[FCC Evaluation Tool - Team B - Emma Ward.xls]Sharron Dyke'!$F$236+'I:\CCR\SAM\IPP\FCC Team B\July 2007\Emma Ward\[FCC Evaluation Tool - Team B - Emma Ward.xls]Sharron Dyke'!$F$270) Regards, JDB |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula Too Long error
How would I collect the links into a table on a hidden sheet? Sorry, being a
bit dim! "JDB" wrote: The formula below works sort of ok, but I now need to change the formula to reflect what will be Augusts results, I'm getting the error formula too long. Is there a way that this can be truncated at all? The formula is as follows - the sheet contains results broken down by team and individual in rows. If anyone can think of a better method for showing results, I'd be glad of that too! =SUM('I:\CCR\SAM\IPP\FCC Team B\July 2007\Emma Ward\[FCC Evaluation Tool - Team B - Emma Ward.xls]Sharron Dyke'!$F$32+'I:\CCR\SAM\IPP\FCC Team B\July 2007\Emma Ward\[FCC Evaluation Tool - Team B - Emma Ward.xls]Sharron Dyke'!$F$66+'I:\CCR\SAM\IPP\FCC Team B\July 2007\Emma Ward\[FCC Evaluation Tool - Team B - Emma Ward.xls]Sharron Dyke'!$F$100+'I:\CCR\SAM\IPP\FCC Team B\July 2007\Emma Ward\[FCC Evaluation Tool - Team B - Emma Ward.xls]Sharron Dyke'!$F$134+'I:\CCR\SAM\IPP\FCC Team B\July 2007\Emma Ward\[FCC Evaluation Tool - Team B - Emma Ward.xls]Sharron Dyke'!$F$168+'I:\CCR\SAM\IPP\FCC Team B\July 2007\Emma Ward\[FCC Evaluation Tool - Team B - Emma Ward.xls]Sharron Dyke'!$F$202+'I:\CCR\SAM\IPP\FCC Team B\July 2007\Emma Ward\[FCC Evaluation Tool - Team B - Emma Ward.xls]Sharron Dyke'!$F$236+'I:\CCR\SAM\IPP\FCC Team B\July 2007\Emma Ward\[FCC Evaluation Tool - Team B - Emma Ward.xls]Sharron Dyke'!$F$270) Regards, JDB |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula Too Long error
Create a new sheet, open the source workbook so you don't have to look at
those nasty file paths, then copy the cells and paste special as link into the new sheet, or type = and click window and the source workbook and cell. Finally when you have all links, create the formula like =SUM('hidden_sheet'!A5:A10) replace hidden_sheet with the real name. Then hide the sheet This is a good routine overall to use overall when calculating other workbooks and it's so much easier to edit the final formula -- Regards, Peo Sjoblom "JDB" wrote in message ... How would I collect the links into a table on a hidden sheet? Sorry, being a bit dim! "JDB" wrote: The formula below works sort of ok, but I now need to change the formula to reflect what will be Augusts results, I'm getting the error formula too long. Is there a way that this can be truncated at all? The formula is as follows - the sheet contains results broken down by team and individual in rows. If anyone can think of a better method for showing results, I'd be glad of that too! =SUM('I:\CCR\SAM\IPP\FCC Team B\July 2007\Emma Ward\[FCC Evaluation Tool - Team B - Emma Ward.xls]Sharron Dyke'!$F$32+'I:\CCR\SAM\IPP\FCC Team B\July 2007\Emma Ward\[FCC Evaluation Tool - Team B - Emma Ward.xls]Sharron Dyke'!$F$66+'I:\CCR\SAM\IPP\FCC Team B\July 2007\Emma Ward\[FCC Evaluation Tool - Team B - Emma Ward.xls]Sharron Dyke'!$F$100+'I:\CCR\SAM\IPP\FCC Team B\July 2007\Emma Ward\[FCC Evaluation Tool - Team B - Emma Ward.xls]Sharron Dyke'!$F$134+'I:\CCR\SAM\IPP\FCC Team B\July 2007\Emma Ward\[FCC Evaluation Tool - Team B - Emma Ward.xls]Sharron Dyke'!$F$168+'I:\CCR\SAM\IPP\FCC Team B\July 2007\Emma Ward\[FCC Evaluation Tool - Team B - Emma Ward.xls]Sharron Dyke'!$F$202+'I:\CCR\SAM\IPP\FCC Team B\July 2007\Emma Ward\[FCC Evaluation Tool - Team B - Emma Ward.xls]Sharron Dyke'!$F$236+'I:\CCR\SAM\IPP\FCC Team B\July 2007\Emma Ward\[FCC Evaluation Tool - Team B - Emma Ward.xls]Sharron Dyke'!$F$270) Regards, JDB |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula Too Long error
Hi
As an afterthought: To hide a sheet, activate it, and then select from menu FormatSheetHide (When you have hidden sheets in workbook, you can unhide any of them selecting FormatSheetUnhide, and then selecting sheet from list) Arvi Laanemets |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| formula too long ERROR | Excel Worksheet Functions | |||
| formula too long error? | Excel Worksheet Functions | |||
| formula is too long error | Excel Worksheet Functions | |||
| Formula too long error... | Excel Discussion (Misc queries) | |||
| formula too long error | Excel Discussion (Misc queries) | |||
Hybrid Mode
