Prev: Finding most recent date for each customer - XL 2007
Next: need to install DVZXLAddin.xla
From: Raj on 29 Apr 2010 23:45
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
From: Bob Phillips on 30 Apr 2010 04:55
Try

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

--

HTH

Bob

"Raj" <rspai9(a)gmail.com> wrote in message
news:6efe9437-8fbf-4fb3-8158-f76059f3b6fb(a)a27g2000prj.googlegroups.com...
> 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


 | 
Pages: 1
Prev: Finding most recent date for each customer - XL 2007
Next: need to install DVZXLAddin.xla