Dynamic sheet reference

S

steve

How do I create a dynamic reference to a sheet throught a specific cell
location?

For example: cell ref!A5 where cell ref is the text contents of a specific
cell like B8.
 
R

Ron Coderre

See the INDIRECT function in Excel Help.

If B8: Sheet 3

Then this formula returns the value of cell A5 on the 'Sheet 3' worksheet:
=INDIRECT("'"&B8&"'!A5")

Note: the quote marks can be confusing....
The left set is: Dbl-quote.....Single-quote...Dbl-quote
The right set is: Dbl-quote...Single-quote...!A5...Dbl-quote

When a sheet name includes spaces, references to it
must be enclosed in single-quotes.

Does that help?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)
 
S

steve

This is perfect. Thanks
--
steve


Ron Coderre said:
See the INDIRECT function in Excel Help.

If B8: Sheet 3

Then this formula returns the value of cell A5 on the 'Sheet 3' worksheet:
=INDIRECT("'"&B8&"'!A5")

Note: the quote marks can be confusing....
The left set is: Dbl-quote.....Single-quote...Dbl-quote
The right set is: Dbl-quote...Single-quote...!A5...Dbl-quote

When a sheet name includes spaces, references to it
must be enclosed in single-quotes.

Does that help?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)
Click to expand...
 
R

Ron Coderre

Glad to help!
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)
 
Top