Let's say that in A1 thru A3 we have:

RGB5
RGB9
B2

Then first install the following UDF:

Function addacross(r1 As Range, r2 As Range, r3 As Range) As Variant
Application.Volatile
Dim s1 As String, s2 As String, s3 As String
s1 = r1.Value
s2 = r2.Value
s3 = r3.Value
doit = False
For i = 1 To Sheets.Count
If Sheets(i).Name = s1 Then
doit = True
End If
If doit Then
addacross = addacross + Sheets(i).Range(s3).Value
End If
If Sheets(i).Name = s2 Then
doit = False
End If
Next
End Function

Next, in an unused cell, enter:

=addacross(A1,A2,A3)

This should give the sum of the B2's in sheets RGB5 thru RGB9.


UDFs are very easy to install and use:

1. ALT-F11 brings up the VBE window
2. ALT-I
ALT-M opens a fresh module
3. paste the stuff in and close the VBE window

If you save the workbook, the UDF will be saved with it.

To use the UDF from the normal Excel window, just enter it like a normal
Excel Function

To remove the UDF:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

To learn more about UDFs, see:

http://www.cpearson.com/excel/Writin...ionsInVBA.aspx


--
Gary''s Student - gsnu2007g


"Richard Buttrey" wrote:

Hi,

I'm struggling to unnderstand what's wrong with the following, and
after googling around I saw one comment that INDIRECT() was somewhat
problematic if using across worksheets.

I have several sheets, the first is named "RGB1" and a later one is
named "RGB9", in between are other sheets with values in B2, all of
which I want to add. There are sheets after RGB9 which I don't want
included.

So the formula

=SUM(RGB1:RGB9!$B$1) works fine.

However for various reasons I want to hold the names of the two sheets
in A1 & A2 since these are variables. I've tried all sorts but am
unable to get an INDIRECT() to work. The obvious

=SUM(INDIRECT(A1&":"&A2&"!"&$B$1)) doesn't work, nor indeed something
like

=SUM(INDIRECT("'" & A1 & ":" & A2 &"'!"&$B$1))

or various other combinations using quotes around the A1 & A2
references.

Any ideas please? Usual TIA

Rgds

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________