1. ## 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. ## 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. ## 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. ## 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
•