In article Ron
wrote:
On Wed, 02 Apr 2008 09:33:45 GMT,
(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
***
I'm using an evaluation license of nemo since 81 days.
You should really try it!
http://www.malcom-mac.com/nemo