Indirect function €“ relative column copy/paste
 

Im using INDIRECT to allow me to refer to a previous months 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 Ive 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 cant think of how to generate this. Any ideas?

Regards

Indirect function €“ relative column copy/paste
 

Gary T wrote:
Im using INDIRECT to allow me to refer to a previous months 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 Ive 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 cant 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)