shorten formula

[Guest]

I have to add onto this formula which is all ready too long
I need to get the sum of 70 worksheets

 

[Bob Phillips]

Put the sheet names in an array C1:C70

=SUMPRODUCT(N(INDIRECT("'"&C1:C70&"'!A1")))

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[RagDyer]

Considering that Bob answered for the worst case scenario, where you have
custom named all your sheets, here's for the best case scenario, where you
have all default (Sheet1, Sheet2, ...etc.) sheet names:

=SUM(SHEET1:SHEET70!A1)

--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

 

[Roger Govier]

Hi
And as a third alternative, create 2 dummy extra sheets called First and Last.
Drag these to positions before your first real sheet and after your last
real sheet and use
=SUM(First:Last!A1)
Of course, the sheet with the formula must be outside of the "sandwich"
created by First and Last.

Regards

Roger Govier

 

[Harlan Grove]

Roger Govier wrote...
....

=SUM(First:Last!A1)
Of course, the sheet with the formula must be outside of the "sandwich"
created by First and Last.

....

No it doesn't. The only restriction on this particular formula is that
it can't be in cell A1 in any of the worksheets between First and Last,
inclusive, without causing circular recalc. It'd work just fine entered
into any other cell.

 

[Bob Phillips]

Perhaps Roger should just have said that it should be outside of the
"sandwich". It may be asking for trouble if it were inside should someone
put a value in cell A1 at some later time.

Bob

 

[Roger Govier]

True, Harlan, but as a generality I think it wiser to suggest that users
keep the sheet with the summation formula(e) outside the "sandwich" to avoid
any chance of circular referencing.
Hopefully it avoids the re-posts with " it doesn't work...."

Regards

Roger Govier

 

[Harlan Grove]

Roger Govier wrote...

True, Harlan, but as a generality I think it wiser to suggest that users
keep the sheet with the summation formula(e) outside the "sandwich" to avoid
any chance of circular referencing.
Hopefully it avoids the re-posts with " it doesn't work...."

....

There's a difference between 'must be' and 'should be'. If you mean
'should', use 'should'.

 

[Roger Govier]

Ouch!!!!!<vbg>
ISC

Regards

Roger Govier

Roger Govier wrote...


....

There's a difference between 'must be' and 'should be'. If you mean
'should', use 'should'.