Formula Too Long error

[Guest]

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

 

[Guest]

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.

 

[Arvi Laanemets]

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.

 

[Guest]

How would I collect the links into a table on a hidden sheet? Sorry, being a
bit dim!

 

[Peo Sjoblom]

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

How would I collect the links into a table on a hidden sheet? Sorry, being
a
bit dim!

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

 

[Arvi Laanemets]

Hi

As an afterthought:
To hide a sheet, activate it, and then select from menu Format>Sheet>Hide
(When you have hidden sheets in workbook, you can unhide any of them
selecting Format>Sheet>Unhide, and then selecting sheet from list)


Arvi Laanemets