I have to add onto this formula which is all ready too long
I need to get the sum of 70 worksheets
I have to add onto this formula which is all ready too long
I need to get the sum of 70 worksheets
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" <[email protected]> wrote in message
news:[email protected]...
> I have to add onto this formula which is all ready too long
> I need to get the sum of 70 worksheets
>
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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > I have to add onto this formula which is all ready too long
> > I need to get the sum of 70 worksheets
> >
>
>
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)
>
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.
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" <[email protected]> wrote in message
news:[email protected]...
> 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.
>
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.
>
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'.
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'.
>
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks