Hi,

I need to add together the values of cell H1000 in every sheet in the
workbook.
The trouble is each day there might be a different number of sheets.

Ideally the total value would be returned as a variable so that I can
use it deeper
in for a further calculation, which I just about can manage!

Would be grateful for any help/pointers any kind soul can offer.

Thanks,

Stuart

One way:

Put a blank worksheet named First before the sheets you want to add, and
a blank worksheet name Last after the last sheet you want to add. Then
use

=SUM(First:Last!H1000)

Make sure any added sheets fall between First and Last.

Or, if you want a programming solution, perhaps something like this User
Defined Function:

Public Function SumOfAllSheets(ByVal rRef As Range) As Variant
Dim ws As Worksheet
Dim dTemp As Double
Dim sAddress
Application.Volatile
On Error GoTo ErrHandler
sAddress = rRef.Address
If sAddress = Application.Caller.Address Then
SumOfAllSheets = CVErr(xlErrRef)
Else
For Each ws In Application.Caller.Parent.Parent.Worksheets
dTemp = dTemp + ws.Range(sAddress)
Next ws
SumOfAllSheets = dTemp
End If
Exit Function
ErrHandler:
SumOfAllSheets = CVErr(xlErrValue)
End Function


In article . com,
"Krayten" wrote:

Hi,

I need to add together the values of cell H1000 in every sheet in the
workbook.
The trouble is each day there might be a different number of sheets.

Ideally the total value would be returned as a variable so that I can
use it deeper
in for a further calculation, which I just about can manage!

Would be grateful for any help/pointers any kind soul can offer.

Thanks,

Stuart

You can do that without resorting to VBA. Add a sheet at the front of the
workbook called Start and one at the end of the book called End. Hide these
two sheets. Now you just need to add a formula like...

=SUM(Start:End!H1000)

With start and end hidden any sheets added or deleted will be between these
two sheets and the formula will return the correct value...
--
HTH...

Jim Thomlinson


"Krayten" wrote:

Hi,

I need to add together the values of cell H1000 in every sheet in the
workbook.
The trouble is each day there might be a different number of sheets.

Ideally the total value would be returned as a variable so that I can
use it deeper
in for a further calculation, which I just about can manage!

Would be grateful for any help/pointers any kind soul can offer.

Thanks,

Stuart

Jim, this worked perfectly, though the new hidden sheets don't show up
in the excel object explorer with the older sheet
between them as I expected the values obtained in the testing I've
just completed certainly adds up correctly.

Thanks you for this simple way to solve the problem.

Kind regards,

Stuart