[Seaq]

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

[Bob Bridges[_2_]]

I assume by "tab" you mean a worksheet, and I think you're saying you have
multiple worksheets, right? And I'm only guessing here, but since worksheets
don't have values themselves, by "last tab value" you might mean "the value
of some cell in the last worksheet". If so, you don't mean you want to
"substitute" the value in that cell, you just want to "display" it.

If all that is right, then I guess you want a cell in one worksheet to
display the sum of some cells in a range of other worksheets. And when you
say "according to selection", you mean... Ok, I give up on that one; I don't
know what you mean. Can you straighten me out?

--- "Seaq" wrote:
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.

[OssieMac]

Hi Bob,

What the OP appears to be doing is sum the cells C46 on sheets 04 to 03 (04
and 03 being sheet names). The formula sums C46 on all the sheets from 04 to
03 including any sheets in between them.

However, I don't know how to use a variable name for his sheet names but
thought I would post this explanation in case it helps someone else who does
know how to use a variable for the sheet. (Easy enough in VBA).

--
Regards,

OssieMac


"Bob Bridges" wrote:

I assume by "tab" you mean a worksheet, and I think you're saying you have
multiple worksheets, right? And I'm only guessing here, but since worksheets
don't have values themselves, by "last tab value" you might mean "the value
of some cell in the last worksheet". If so, you don't mean you want to
"substitute" the value in that cell, you just want to "display" it.

If all that is right, then I guess you want a cell in one worksheet to
display the sum of some cells in a range of other worksheets. And when you
say "according to selection", you mean... Ok, I give up on that one; I don't
know what you mean. Can you straighten me out?

--- "Seaq" wrote:
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.

[RagDyeR]

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

[Harlan Grove[_2_]]

"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)")

[Seaq]

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)")