Indirect Function() - summing across sheets
Richard Buttrey
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
__________________________
Richard
<47f3502...@news.zen.co.uk>chaos.theory.no...@zen.co.u
k (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
> somewhatproblematic 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
> ofwhich 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
> amunable 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
¾ __________________________
Sorry,
Should have referred in the formula to B2 not B1.
Mea culpa.
I'm using an evaluation license of nemo since 81 days.
You should really try it!
http://www.malcom-mac.com/nemo
Gary''s Student
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/WritingFunctionsInVBA.aspx
--
Gary''s Student - gsnu2007g
Ron Rosenfeld
I do not believe that INDIRECT can be used to construct a 3D reference.
However, with certain constraints, Laurent Longre (author of morefunc.xll, a
very useful add-in) discovered that you can use INDIRECT to construct an array
of references, which can have a similar result.
For example, with a number 2 in A1, and number 9 in A2, the following will SUM
the values in Sheet2:Sheet9!B1:
=SUMPRODUCT(N(INDIRECT("Sheet"&ROW(INDIRECT(A1&":"&A2))&"!"&ADDRESS(1,2))))
The argument for the INDIRECT function resolves into this array:
{"Sheet2!$B$1";"Sheet3!$B$1";"Sheet4!$B$1";"Sheet5!$B$1";"Sheet6!$B$1";"Sheet7!$B$1";"Sheet8!$B$1";"Sheet9!$B$1"}
The N function is required -- INDIRECT won't pass the values without it. Don't
know why.
You could use the SUM function instead of SUMPRODUCT but, at least in Excel
2007, you would have to enter the formula as an array-formula (e.g. with
<ctrl><shift><enter>).
In your example, assuming your RGB sheets are consecutively numbered, you could
substitute "RGB" for "Sheet".
Obviously there are other methods of constructing the required array.
If the sheet names were not related by a simple numbering scheme, you could
enter the sheet names individually into a1:an and use something like:
=SUMPRODUCT(N(INDIRECT(A1:An&"!"&ADDRESS(1,2))))
But if there are any empty entries (or invalid sheetnames) in A1:An, you will
probably get a #REF! error
--ron
Richard
Rosenfeld<ronros...@nospam.org> wrote:
> On Wed, 02 Apr 2008 09:33:45 GMT,
> chaos.theory.no...@zen.co.uk(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
>> somewhatproblematic 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
>> ofwhich 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 amunable to get an INDIRECT() to work. The obvious
>> =SUM(INDIRECT(A1&":"&A2&"!"&$B$1)) doesn't work, nor indeed
>> somethinglike
>> =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
¾¾ __________________________
> I do not believe that INDIRECT can be used to construct a 3D
> reference. However, with certain constraints, Laurent Longre (author
> of morefunc.xll, a very useful add-in) discovered that you can use
> INDIRECT to construct an arrayof references, which can have a similar
> result.
> For example, with a number 2 in A1, and number 9 in A2, the
> following will SUMthe values in Sheet2:Sheet9!B1:
> =SUMPRODUCT(N(INDIRECT("Sheet"&ROW(INDIRECT(A1&":"&A2))&"!"&ADDRESS(1,
¾ 2¨¨¨¨
> The argument for the INDIRECT function resolves into this array:
> {"Sheet2!$B$1";"Sheet3!$B$1";"Sheet4!$B$1";"Sheet5!$B$1";"Sheet6!$B$1"
> ;"Sheet7!$B$1";"Sheet8!$B$1";"Sheet9!$B$1"}
> The N function is required -- INDIRECT won't pass the values without
> it. Don'tknow why.
> You could use the SUM function instead of SUMPRODUCT but, at least
> in Excel 2007, you would have to enter the formula as an
> array-formula (e.g. with<ctrl><shift><enter>).
> In your example, assuming your RGB sheets are consecutively
> numbered, you couldsubstitute "RGB" for "Sheet".
> Obviously there are other methods of constructing the required
> array.
> If the sheet names were not related by a simple numbering scheme,
> you couldenter the sheet names individually into a1:an and use
> something
> like:
> =SUMPRODUCT(N(INDIRECT(A1:An&"!"&ADDRESS(1,2))))
> But if there are any empty entries (or invalid sheetnames) in A1:An,
> you willprobably get a #REF! error
> --ron
Thanks for the detailed response Ron,
The first mentioned possible solution
=SUMPRODUCT(N(INDIRECT("Sheet"&ROW(INDIRECT(A1&":"&A2))&"!"&ADDRESS(1,
2¨¨¨¨
Seems to work to a point, but unless I've misunderstood something,
this appears to work with the VBA sheet names rather than the tab
names. For instance in my test workbook, the sheets left to right have
tab names of Sheet2, Sheet3, Sheet4, Sheet5 and Sheet9, however VBA
(and presumably the Indirect function, knows these as Sheet2, Sheet4,
Sheet9, Sheet5 and Sheet3, presumably because I've been changing the
names and order whilst attempting to get to grips with this problem.
i.e. the straightforward non indirect function adds up all 5 sheets
because Sheet2 & Sheet5 are the first and last in the order, whereas
the Indirect() function is only summing the first 4 sheets.
Is there any modification I can make to have the ...Indirect() formula
total the same as the non Indirect version?
Thanks once more,
Richard
Richard
> RGB5
> RGB9
> B2
> =addacross(A1,A2,A3)
> To remove the UDF:
> http://www.cpearson.com/excel/WritingFunctionsInVBA.aspx
Hi,
Thanks for that,
What's the reference to 'doit' in the VBA code?
I'm using a Mac at the moment which seems to complain about this - it
thinks it's an undeclared variable. However from memory I can't
recall that as a keyword from my PC VBA days.
I'll dig out an old PC shortly and try the UDF on that.
Many thanks
Richard
I'm using an evaluation license of nemo since 82 days.
Ron Rosenfeld
>Thanks for the detailed response Ron,
>
>The first mentioned possible solution
>
>=SUMPRODUCT(N(INDIRECT("Sheet"&ROW(INDIRECT(A1&":"&A2))&"!"&ADDRESS(1,
> 2¨¨¨¨
>
>Seems to work to a point, but unless I've misunderstood something,
>this appears to work with the VBA sheet names rather than the tab
>names. For instance in my test workbook, the sheets left to right have
>tab names of Sheet2, Sheet3, Sheet4, Sheet5 and Sheet9, however VBA
>(and presumably the Indirect function, knows these as Sheet2, Sheet4,
>Sheet9, Sheet5 and Sheet3, presumably because I've been changing the
>names and order whilst attempting to get to grips with this problem.
>
>i.e. the straightforward non indirect function adds up all 5 sheets
>because Sheet2 & Sheet5 are the first and last in the order, whereas
>the Indirect() function is only summing the first 4 sheets.
>
>Is there any modification I can make to have the ...Indirect() formula
>total the same as the non Indirect version?
>
>Thanks once more,
>
>Richard
The INDIRECT function should be working on the actual names, not the VBA names.
IT is also NOT constructing a 3D reference, but rather an array of individual
references.
The problem with your use of the first approach to your list, is that your
sheets are not consecutively numbered, so when you construct your array using
the ROW(INDIRECT(... function, you will wind up with some illegal references:
E.G.
A1: 2
A2: 9
{"Sheet2!$B$1";"Sheet3!$B$1";"Sheet4!$B$1";"Sheet5!$B$1";"Sheet6!$B$1";"Sheet7!$B$1";"Sheet8!$B$1";"Sheet9!$B$1"}
Since your actual (on the Excel Tab) names are NOT related by a simple
numbering scheme, you could use my second method, where you list the sheet
names individually in A1:An, and then refer to that range in the formula.
EG:
A1: Sheet2
A2: Sheet3
A3: Sheet4
A4: Sheet5
A5: Sheet9
Then use:
=SUMPRODUCT(N(INDIRECT(A1:A5&"!"&ADDRESS(1,2))))
to sum all the B1's in those sheets.
Or you may be able to develop the appropriate array differently.
OR you may be able to number/name your sheets sequentially.
--ron
Gary''s Student
Dim doit as Boolean
after the other dim statement
--
Gary''s Student - gsnu2007g