Results 1 to 4 of 4
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Houston, Texas, USA
    Posts
    432
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Adding up sheets (Excel 97)

    We have a workbook that has about 65 different sheets. In the 1st sheet me add up the totals from cell ay5 on each sheet. Here is an example of what the cell formula looks like:

    =SUM('AJ MARTELINO'!AY5+'ANGELA HARMON'!AY5+'A TRIHN TRIHN'!BG5+'BETTY GARCIA'!BG5+'BLANCA RODRIGUEZ'!AY5+'CHERYL HUNT'!AY5+'CYNTHIA PRESTON'!AY5+'DEANNA DOZIER'!AY5+'DIALLO HOLTS'!AY5+'DIANA COTTONHAM'!AY5+'DIANNE JONES'!AY5+'DOROTHY ANDERSON'!AY5+'ELENOR JACKSON'!AY5+'GWENDOLYN TITTLE'!AY5+'JACQUELINE SMITH'!AY5+'JAHNSHANNA WHITE'!AY5+'JOANNA IBARRA'!AY5+'KAREN KRUPINSKI'!AY5+'KATIE SMITH'!AY5+'LATONIA LOTTS'!AY5+'LORA CLACK'!AY5+'MARY BORDEAUX'!AY5+'MARYSOL CUEVAS'!AY5+'MAUREEN MCMINN'!AY5+'MICHELLE TRAYLOR'!AY5+'MOON TRAN'!AY5+'NATALIE BUNKERS'!AY5+'NICOLE RIGGS'!AY5+'OLYMPIA HURRINGTON'!AY5+'PENNIE RITCHIE'!AY5+'RAVEN JONES'!AY5+'ROBYN BENDER'!AY5+'RYANNE MARBEL'!AY5+'SANDRA CARTER'!AY5+'SHANNON FITZGERALD'!AY5+'SHARON MCBRIDE-BALTIMORE'!AY5+'SOPHIA ROBERTSON'!AY5+'TABS TRAINING ID'!AY5+'TANISHA DANEY'!AY5+'TIFFANY WALKER'!AY5+'TONJA WILSON'!AY5+'VALERIE GILBERT'!AY5+'VIJAYA SRUNGARAM'!AY5+'VON MARTI'!AY5+'XAVIERA CHAMBERS'!AY5+'NEW USER1'!AY5+'NEW USER 2'!AY5+'NEW USER 3'!AY5+'NEW USER 4'!AY5)

    At around the 50th cell it stops and other 15 are not processed. Each sheet is named after the person. This is done so we can take that name and use it for a column heading. Is there someway we can shorten this so we can read the rest of the 65 users info.?

  2. #2
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    120
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding up sheets (Excel 97)

    The syntax to sum the same cell on multiple sheets:

    =SUM(Sheet1:Sheet5!A1)

    This will add the values in cell A1 for all sheets from sheet1 to sheet5.

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Ankeny, Iowa, USA
    Posts
    298
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding up sheets (Excel 97)

    Would it work to do this:

    On the sheet with the totals, type the following in the cell you want the total to appear =sum( and then click on the first sheet, scroll over to the last sheet and hold shift and click on it to select all of the sheets, select the cell you want to sum (ie AY5) and then hit enter.

    If your sheets were named Sheet1 up to Sheet65, you would enter this formula in Sheet66 and it would look like this =SUM(Sheet1:Sheet65!AY5)

  4. #4
    New Lounger
    Join Date
    Dec 2000
    Location
    Indiana
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding up sheets (Excel 97)

    A user defined function like this one may work.

    Function USD_SUMOFCELL_AY5()
    Dim XSUM
    Dim SH

    XSUM = 0

    For Each SH In ActiveWorkbook.Sheets
    XSUM = XSUM + SH.Range("AY5")
    Next SH
    USD_SUMOFCELL_AY5 = XSUM

    End Function


    Reference it from any worksheet with the formula
    =USD_SUMOFCELL_AY5()

Posting Permissions

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