On 02 Apr 2008 13:38:29 GMT, Richard wrote:

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