Using data in cell for sheet referencing in formulas

[MS_user]

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!!

 

[RagDyer]

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

 

[Wigi]

You need to use the INDIRECT function.

 

[MS_user]

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