Harlan

Can you help to see if i have made the right steps:

1. right click on tab and select view code and paste in

Function evaludf(s As String) As Variant
evaludf = Evaluate(s)
End Function

2. input into the WB the formula

=evaludf("SUM('"& x &":"& y &"'!C46)")

where x, y should be replace by the cell address for input starting and
ending tab names.

What I got is a #Name error, what else should i try?

Rgds,

"Harlan Grove" wrote:

"Ragdyer" wrote...
XL is not too strong with 3D functions.

Because Excel really isn't a 3D spreadsheet. Excel workbooks are just
collections of 2D worksheets. 3D references are just syntactic sugar
available only in a FEW worksheet functions (they can't be passed to
VBA udfs, though they can be passed to XLL add-ins).

To work out something like you're looking for, you'll need to make a list of
your individual sheet names.
....

Now, say you wish to total C46 on sheet 01 to sheet 04:

=SUMPRODUCT(N(INDIRECT("'"&A1:A4&"'!C46")))
....

This works because you're summing a single cell in each worksheet.
This doesn't generalize to multiple cell ranges in each worksheet.

If there were only a relatively few possible sets of worksheets, e.g.,
always begin with the worksheet named 04, but sum either 04:04, 04:03,
04:02, 04:01 or 04:00, then the following approach generalizes to some
extent.

=SUM(CHOOSE(x+1,'04:00'!C46,'04:01'!C46,'04:02'!C4 6,'04:03'!
C46,'04:04'!C46))

If maximum generality is needed AND udfs are OK, add the following
code to a GENERAL VBA module

Function evaludf(s As String) As Variant
evaludf = Evaluate(s)
End Function

and use it in formulas like

=evaludf("SUM('04:"& x &"'!C46)")

or

=evaludf("SUM('"& x &":"& y &"'!C46)")