Need formula for sheet & cell reference

M

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
 
M

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




Gilbert De Ceulaer said:
A1 = sheetname
G10 = cell
Then formula is = indirect(" ' "& A1 & " '! "&g10)
OK
Gilbert
[email protected]

MPH said:
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
Click to expand...
Click to expand...
 
D

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

Gilbert De Ceulaer said:
A1 = sheetname
G10 = cell
Then formula is = indirect(" ' "& A1 & " '! "&g10)
OK
Gilbert
[email protected]

MPH said:
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
Click to expand...
Click to expand...
Click to expand...
 
M

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.

Dave Peterson said:
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

Gilbert De Ceulaer said:
A1 = sheetname
G10 = cell
Then formula is = indirect(" ' "& A1 & " '! "&g10)
OK
Gilbert
[email protected]

Help
Have a list of sheet names and want to create a formula to use
Click to expand...
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
Click to expand...
Click to expand...
Click to expand...
 
Top