Need formula for sheet & cell reference

[MPH]

Help
Have a list of sheet names and want to create a formula to use sheet name
as a reference in a formula. Have tried "Indirect" but haven't succeeded
with that or any other.
For example

sheet names listed vertically i.e. inv1, inv1, inv3 etc.
formula needed to refer to "sheet name"[Inv1] AND "cell reference"[g10]

Have used SheetOffset in VBA but want to use actual sheet name, rather than
offset.
For this file it is not possible to use consolidation feature.

Will appreciate any help.
TIA
Malcolm Hughes

 

[Gilbert De Ceulaer]

A1 = sheetname
G10 = cell
Then formula is = indirect(" ' "& A1 & " '! "&g10)
OK
Gilbert
(e-mail address removed)

 

[MPH]

Thanks, but
That solution produces #REF.
The G10 I want is the G10 in sheetname i.e. sheetname x, cell G10;
sheetname y, cell G10 etc.
Still confused.
Thanks

A1 = sheetname
G10 = cell
Then formula is = indirect(" ' "& A1 & " '! "&g10)
OK
Gilbert
(e-mail address removed)

Help
Have a list of sheet names and want to create a formula to use sheet name
as a reference in a formula. Have tried "Indirect" but haven't succeeded
with that or any other.
For example

sheet names listed vertically i.e. inv1, inv1, inv3 etc.
formula needed to refer to "sheet name"[Inv1] AND "cell reference"[g10]

Have used SheetOffset in VBA but want to use actual sheet name, rather
than
offset.
For this file it is not possible to use consolidation feature.

Will appreciate any help.
TIA
Malcolm Hughes

 

[Dave Peterson]

How about:

=INDIRECT("'"&A1&"'!g10")

Thanks, but
That solution produces #REF.
The G10 I want is the G10 in sheetname i.e. sheetname x, cell G10;
sheetname y, cell G10 etc.
Still confused.
Thanks

A1 = sheetname
G10 = cell
Then formula is = indirect(" ' "& A1 & " '! "&g10)
OK
Gilbert
(e-mail address removed)

Help
Have a list of sheet names and want to create a formula to use sheet name
as a reference in a formula. Have tried "Indirect" but haven't succeeded
with that or any other.
For example

sheet names listed vertically i.e. inv1, inv1, inv3 etc.
formula needed to refer to "sheet name"[Inv1] AND "cell reference"[g10]

Have used SheetOffset in VBA but want to use actual sheet name, rather
than
offset.
For this file it is not possible to use consolidation feature.

Will appreciate any help.
TIA
Malcolm Hughes

 

[MPH]

That works fine, thanks a lot.
Please disregard earlier message concerning the formula not copying as
expected; had calc switched off.

Many thanks for your assistance.

How about:

=INDIRECT("'"&A1&"'!g10")

Thanks, but
That solution produces #REF.
The G10 I want is the G10 in sheetname i.e. sheetname x, cell G10;
sheetname y, cell G10 etc.
Still confused.
Thanks

A1 = sheetname
G10 = cell
Then formula is = indirect(" ' "& A1 & " '! "&g10)
OK
Gilbert
(e-mail address removed)

Help
Have a list of sheet names and want to create a formula to use

sheet
name

as a reference in a formula. Have tried "Indirect" but haven't succeeded
with that or any other.
For example

sheet names listed vertically i.e. inv1, inv1, inv3 etc.
formula needed to refer to "sheet name"[Inv1] AND "cell reference"[g10]

Have used SheetOffset in VBA but want to use actual sheet name, rather
than
offset.
For this file it is not possible to use consolidation feature.

Will appreciate any help.
TIA
Malcolm Hughes