Using Indirect en direct cell references

Hi all,

I want to use a range on another worksheet. For this the worksheet name is
named in a cell G1 (called Jan2005), within this worksheet i have a big
matrix and the management info is extracted by row, so the formula looks like
this.

=SUM((INDIRECT($G$1&"!O52:X52")))

So far so good. Now I want to copy this formula down to re-use the formula,
as the management info is by row, I want the 052:X52 to adjust, so actually I
want to set them outside the indirect influece and no longer be text, but a
real range.
Something like this, but it doesn't work

=SUM((INDIRECT($G$1&"!")O52:X52))

I can't this get to work, anybody any idea ?

=SUM(INDIRECT("'"&$G$1&"'!"&SUBSTITUTE(CELL("Address",($A$1,O53:X53)),"$A$1,","")))

Bart Schouw wrote:
> Hi all,
>
> I want to use a range on another worksheet. For this the worksheet name is
> named in a cell G1 (called Jan2005), within this worksheet i have a big
> matrix and the management info is extracted by row, so the formula looks like
> this.
>
> =SUM((INDIRECT($G$1&"!O52:X52")))
>
> So far so good. Now I want to copy this formula down to re-use the formula,
> as the management info is by row, I want the 052:X52 to adjust, so actually I
> want to set them outside the indirect influece and no longer be text, but a
> real range.
> Something like this, but it doesn't work
>
> =SUM((INDIRECT($G$1&"!")O52:X52))
>
> I can't this get to work, anybody any idea ?

Hi Aladin,

Thanks it works, altough I wonder if there is an easier solution to this, it
seems a little artifical.

Best regards
Bart

"Aladin Akyurek" wrote:

> =SUM(INDIRECT("'"&$G$1&"'!"&SUBSTITUTE(CELL("Address",($A$1,O53:X53)),"$A$1,","")))
>
> Bart Schouw wrote:
> > Hi all,
> >
> > I want to use a range on another worksheet. For this the worksheet name is
> > named in a cell G1 (called Jan2005), within this worksheet i have a big
> > matrix and the management info is extracted by row, so the formula looks like
> > this.
> >
> > =SUM((INDIRECT($G$1&"!O52:X52")))
> >
> > So far so good. Now I want to copy this formula down to re-use the formula,
> > as the management info is by row, I want the 052:X52 to adjust, so actually I
> > want to set them outside the indirect influece and no longer be text, but a
> > real range.
> > Something like this, but it doesn't work
> >
> > =SUM((INDIRECT($G$1&"!")O52:X52))
> >
> > I can't this get to work, anybody any idea ?
>