XL is not too strong with 3D functions.

To work out something like you're looking for, you'll need to make a list of
your individual sheet names.
You can then refer to the cell addresses in this list to change the range of
the sheets you want to total..

Say your sheet names are in A1 to A9.
Make sure the spelling is *exactly* the same as on the tab.

A1 - 01
A2 - 02
A3 - 03
A4 - 04
etc....

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

=SUMPRODUCT(N(INDIRECT("'"&A1:A4&"'!C46")))
OR
sheet 03 to sheet 06
=SUMPRODUCT(N(INDIRECT("'"&A3:A6&"'!C46")))

Take note! ... this does *not* total sheets that are "sandwiched" between 03
and 06!
Just the sheets referenced within the cell range of A3 to A6.
If your sheets were in the tabbed order in your WB:
03
04
08
09
05
06
sheets 08 and 09 would *not* be included in the total like they would be if
you used this formula:
=SUM('03:06'!C46)

You could also assign names to various portions of your sheet list in A1:A9,
and reference those names within the formula.

Say you named A1 to A3 as "three",
and A1 to A6 as "six",
and A1 to A9 as "nine",

Then these would sum the sheets referred to in the list:

=SUMPRODUCT(N(INDIRECT("'"&three&"'!C46")))
=SUMPRODUCT(N(INDIRECT("'"&six&"'!C46")))
=SUMPRODUCT(N(INDIRECT("'"&nine&"'!C46")))

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------


"Seaq" wrote in message
...
Hi, I am trying to figure out how to substitute the ending tab value by
the
value in another cell

i.e. =SUM('04:03'!C46) , where tab name "03" need to change according to
selection.

please help! Thanks in advance