I have a multi sheet spreadhseet in which the Sheet reference (from which i
require data) is held as cell data.

For example in Sheet 2 i have a reference in a cell A3 which contains the
data "Sheet 1". in attempting to dynamically access this sheet in a formula i
am trying to use A3 instead of the standard Sheet 1 that is automatically
used.

i.e. I am trying to replace formula is Sheet 2 from =SUM(Sheet1!H1:H9) to
dynamically reference Sheet 1, something along the lines of =SUM(A3!H1:H9).

However using the dynamic cell reference A3 - it does not work!! Any ideas
on how to resolve this. I have tried absolute referencing, using single and
double quotes, but unable to achieve the desired result. Any assistance most
welcome!!

Use the Indirect() function:

=SUM(INDIRECT(A3&"!H1:H9"))

HOWEVER, if your sheet names have spaces in them,
Or even if they presently *don't*,
it's safer to include the single quotes for safety,
which will work with names that do and/or don't have them:

=SUM(INDIRECT("'"&A3&"'!H1:H9"))

--
HTH,

RD

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

"MS_user" wrote in message
...
I have a multi sheet spreadhseet in which the Sheet reference (from which i
require data) is held as cell data.

For example in Sheet 2 i have a reference in a cell A3 which contains the
data "Sheet 1". in attempting to dynamically access this sheet in a
formula i
am trying to use A3 instead of the standard Sheet 1 that is automatically
used.

i.e. I am trying to replace formula is Sheet 2 from =SUM(Sheet1!H1:H9) to
dynamically reference Sheet 1, something along the lines of
=SUM(A3!H1:H9).

However using the dynamic cell reference A3 - it does not work!! Any ideas
on how to resolve this. I have tried absolute referencing, using single
and
double quotes, but unable to achieve the desired result. Any assistance
most
welcome!!

You need to use the INDIRECT function.


--
Wigi
http://www.wimgielis.be = Excel/VBA, soccer and music


"MS_user" wrote:

I have a multi sheet spreadhseet in which the Sheet reference (from which i
require data) is held as cell data.

For example in Sheet 2 i have a reference in a cell A3 which contains the
data "Sheet 1". in attempting to dynamically access this sheet in a formula i
am trying to use A3 instead of the standard Sheet 1 that is automatically
used.

i.e. I am trying to replace formula is Sheet 2 from =SUM(Sheet1!H1:H9) to
dynamically reference Sheet 1, something along the lines of =SUM(A3!H1:H9).

However using the dynamic cell reference A3 - it does not work!! Any ideas
on how to resolve this. I have tried absolute referencing, using single and
double quotes, but unable to achieve the desired result. Any assistance most
welcome!!

Any idea on what the syntax would be for the formula line? How is cell A3
referenced with the INDIRECT function?

So =SUM(Sheet1!H1:H9) becomes....................? (where cell A3 contains
the value Sheet1)

Many thanks

"Wigi" wrote:

You need to use the INDIRECT function.


--
Wigi
http://www.wimgielis.be = Excel/VBA, soccer and music


"MS_user" wrote:

I have a multi sheet spreadhseet in which the Sheet reference (from which i
require data) is held as cell data.

For example in Sheet 2 i have a reference in a cell A3 which contains the
data "Sheet 1". in attempting to dynamically access this sheet in a formula i
am trying to use A3 instead of the standard Sheet 1 that is automatically
used.

i.e. I am trying to replace formula is Sheet 2 from =SUM(Sheet1!H1:H9) to
dynamically reference Sheet 1, something along the lines of =SUM(A3!H1:H9).

However using the dynamic cell reference A3 - it does not work!! Any ideas
on how to resolve this. I have tried absolute referencing, using single and
double quotes, but unable to achieve the desired result. Any assistance most
welcome!!