Indirect Function() - summing across sheets

[Richard Buttrey]

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
__________________________

 

[Richard]

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

¾  __ 
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

 

[Ron Rosenfeld]

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
__________________________

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

 

[Gary''s Student]

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/WritingFunctionsInVBA.aspx

 

[Richard]

On Wed, 02 Apr 2008 09:33:45 GMT,

¾¾  __  ¾¾  __________________________

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:

¾  2¨¨¨¨

The argument for the INDIRECT function resolves into this array:


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:

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

­­ 
I'm using an evaluation license of nemo since 81 days.
You should really try it!
http://www.malcom-mac.com/nemo

 

[Richard]

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

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:

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

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.
You should really try it!
http://www.malcom-mac.com/nemo

 

[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]

It is just a Boolean:

Dim doit as Boolean

after the other dim statement