Prev: Custom list limited to 255 characters? (XL 2007)
Next: Blanks ("") by Formula
From: vinu on 28 Apr 2010 08:29
Hi,

I'm just preparing a summery sheet for an work book that contains more
then 60sheets (date wise)

In Column B7 - Sheet Name(i.e. 26th April

In Column C7 to G7 - Array Formula (i.e. {='26th April'!C21:G21})

In Column J7 to N7 - Array Formula (i.e. {='26th April'!J20:N20})

Is there any way to change the formula in a way to just drag down the
formula by referring B cell instead of typing the sheet name in the
formula.

Thanks in anticipation....

Vinu.
From: Pete_UK on 28 Apr 2010 08:45
You can use INDIRECT to build up a reference in the way you describe:

=INDIRECT("'"&$B7&"'!C21")

Note that as the C21 is within a text string, it will not change when
you copy the formula down or across, whereas the $B7 will change when
you copy it down (and hence pick the next sheet names).

Hope this helps.

Pete

On Apr 28, 1:29 pm, vinu <vinup...(a)gmail.com> wrote:
> Hi,
>
> I'm just preparing a summery sheet for an work book that contains more
> then 60sheets (date wise)
>
> In Column B7 - Sheet Name(i.e. 26th April
>
> In Column C7 to G7 - Array Formula (i.e. {='26th April'!C21:G21})
>
> In Column J7 to N7 - Array Formula (i.e. {='26th April'!J20:N20})
>
> Is there any way to change the formula in a way to just drag down the
> formula by referring B cell instead of typing the sheet name in the
> formula.
>
> Thanks in anticipation....
>
> Vinu.

From: vinu on 28 Apr 2010 09:12
Hi Pete,

Thanks a lot for the quick response.

Perfectly fits my requirement.

Regards,
Vinu
From: Pete_UK on 28 Apr 2010 09:36
You're welcome, Vinu - thanks for feeding back.

Pete

On Apr 28, 2:12 pm, vinu <vinup...(a)gmail.com> wrote:
> Hi Pete,
>
> Thanks a lot for the quick response.
>
> Perfectly fits my requirement.
>
> Regards,
> Vinu

 | 
Pages: 1
Prev: Custom list limited to 255 characters? (XL 2007)
Next: Blanks ("") by Formula