+ Reply to Thread
Results 1 to 9 of 9

shorten formula

  1. #1
    PACable
    Guest

    shorten formula

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


  2. #2
    Bob Phillips
    Guest

    Re: 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" <[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
    >




  3. #3
    RagDyer
    Guest

    Re: 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" <[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
    > >

    >
    >



  4. #4
    Roger Govier
    Guest

    Re: 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)
    >


  5. #5
    Harlan Grove
    Guest

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


  6. #6
    Bob Phillips
    Guest

    Re: 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" <[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.
    >




  7. #7
    Roger Govier
    Guest

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


  8. #8
    Harlan Grove
    Guest

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


  9. #9
    Roger Govier
    Guest

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


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1