Dynamic sheet reference in Name formula

[Raj]

Hi,

The name "CurrentMonth" is defined using the following formula:

=OFFSET('Apr-10'!$H$5,0,0,COUNTA('Apr-10'!$H$5:$H$500),20)

The workbook has 12 sheets with names from Apr-10 to Mar-11. These
will be placed in a data validation list in cell A1

Requirement: The sheet name in the formula to dynamically change with
the value selected in cell A1. ie if the value in cell A1 is Jun-10
then the sheet name i the formula should be replaced with Jun-10. Is
this possible or any other way to achieve this?

Thanks in advance for the help.

Regards.
Raj

 

[Bob Phillips]

Try

=OFFSET(INDIRECT("'"&$A$1&"'!$H$5"),0,0,COUNTA(INDIRECT("'"&$A$1&"'!$H$5:$H$500")),20)