Indirect function – relative column copy/paste

[Gary T]

I’m using INDIRECT to allow me to refer to a previous month’s cells (thanks
to helpful answers to another query I posted on these forums).

The INDIRECT portion of the formula is currently:

INDIRECT("'"&TEXT($A$1-1,"mmmm")&" "&TEXT($A$1-1,"yyyy")&"'!"&"AB$3:AB$358")

And I’ve entered the whole formula into cell IC3.

The above INDIRECT formula allows me to perform functions on the range:

'May 2008'!AB$3:AB$358

(cell $A$1 that the indirect function refers to contains the date 01-Jun-08)

What I would like to do is copy the formula in IC3 across to QB3, but for
the column reference to change relatively (i.e. so that the formula in ID3
refers to AC$3:AC$358, the formula in IE3 refers to AD$3:AD$358, and so on).

However, I can’t think of how to generate this. Any ideas?

Regards

 

[Glenn]

I’m using INDIRECT to allow me to refer to a previous month’s cells (thanks
to helpful answers to another query I posted on these forums).

The INDIRECT portion of the formula is currently:

INDIRECT("'"&TEXT($A$1-1,"mmmm")&" "&TEXT($A$1-1,"yyyy")&"'!"&"AB$3:AB$358")

And I’ve entered the whole formula into cell IC3.

The above INDIRECT formula allows me to perform functions on the range:

'May 2008'!AB$3:AB$358

(cell $A$1 that the indirect function refers to contains the date 01-Jun-08)

What I would like to do is copy the formula in IC3 across to QB3, but for
the column reference to change relatively (i.e. so that the formula in ID3
refers to AC$3:AC$358, the formula in IE3 refers to AD$3:AD$358, and so on).

However, I can’t think of how to generate this. Any ideas?

Regards

=INDIRECT("'"&TEXT($A$1-1,"mmmm")&" "&TEXT($A$1-1,"yyyy")
&"'!"&"R3C"&COLUMN()-209&":R358C"&COLUMN()-209,FALSE)