shorten formula
 

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

shorten formula
 

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)


"PACable" wrote in message
...
I have to add onto this formula which is all ready too long
I need to get the sum of 70 worksheets

shorten formula
 

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!
==============================================


"Bob Phillips" wrote in message
...
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)


"PACable" wrote in message
...
I have to add onto this formula which is all ready too long
I need to get the sum of 70 worksheets

shorten formula
 

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


RagDyer wrote:
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)

shorten formula
 

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.

shorten formula
 

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

"Harlan Grove" wrote in message
ups.com...
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.

shorten formula
 

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 wrote:
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.

shorten formula
 

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'.

shorten formula
 

Ouch!!!!!<vbg
ISC

Regards

Roger Govier


Harlan Grove wrote:
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'.