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 <(E-Mail Removed)>,
"Krayten" <(E-Mail Removed)> 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