add sheet values

[geebee]

hi,

i would like to know how i can havea summary sheet which takes all the
values from cell C2 frmo each sheet and add them up on the summary sheet.

the problem is that people can add sheets dynamically like there could be
anywhere from 1 to 10 sheets. but the sheet names can be like sheet1,
sheet2, etc.

i would like for the cell on the summary sheet to be something like ...
=sheet1!C2 + sheet2!C2 and so forth.

but i want the formula to go all the way up to indlue cells from sheet10. i
would like to know how i can change this formula dynamically so that it
reflects adding up cell C2 from 2 sheets, etc. all the way up to 10 sheets.

thanks in advance,
geebee

 

[Gary''s Student]

Use this UDF:

Function zum() As Variant
Application.Volatile
zum = 0
For Each w In Worksheets
zum = zum + w.Range("C2").Value
Next
End Function

 

[Gord Dibben]

Easiest methos is to create a sandwich with the sheets as the filler.

Assuming Summary sheet is first in workbook.

Insert a dummy sheet to the right of Summary sheet..........name it Start

Insert a dummy sheet at end..........name it End

In your summary sheet =SUM(Start:End!C2)

When adding new sheets insert them between Start and End.

Sheetnames can be anything.


Gord Dibben MS Excel MVP

 

[geebee]

hi,

thanks. now i am just trying to figure out how to display the zum value in
a cell. i typed =zum

but it doesnt work

thanks in advance,
geebee

 

[Gary''s Student]

=zum()

 

[geebee]

hi,

ok lets just say that we do not want it to include the values from a few of
the sheets. how do we add the code to state which sheets it should not take
values from?

thanks in advance,
geebee