=I have several columns of data

I am trying to put a formula in A3 that will read say, =D5
When i drag this down on autofill it then reads =D6 (in cell A4), =D7(in
cell A5), =D8 (in cell A6) etc
Is it possible on autofill to get it to read =E5, =F5, =G5, =H5, =I5 etc as
i drag the formula down?

Thanks in advance!

Richard

Here's a "quick & dirty"
in any cell in a clear row type your =D$5
then autofill the cell across the columns ( sideways) now they update to read
=E$5 =F$5 =G$5 ...etc
scroll over the row
now go to EDITREPLACE
=
with =$
they will now be absolute ( =$E$5 =$F$5 ....)
now you can copy and paste special transpose them where you need them...

I'm sure there is a better way, but I'm not the one with that knowledge...
HTH



"Rich Hayes" wrote:

=I have several columns of data

I am trying to put a formula in A3 that will read say, =D5
When i drag this down on autofill it then reads =D6 (in cell A4), =D7(in
cell A5), =D8 (in cell A6) etc
Is it possible on autofill to get it to read =E5, =F5, =G5, =H5, =I5 etc as
i drag the formula down?

Thanks in advance!

Richard

Sure - this is tip #72 from my book, "This isn't Excel, it's Magic"
(http://www.iil.com/iil/excelmagic)
Several steps:
1 - in A3, enter R5C4 (standing for Row 5 column 4, or D5) without the "="
2 - Fill this down with the fill handle - this will change it to R5C5, R5C6,
etc.
3 - Tools/Options/General tab, click R1C1
4 - Select these R5Cx cells and use Edit/Replace, changing R to =R
5 - Tools/Options/General tab, deselect R1C1
you're done.
HTH

"Rich Hayes" wrote:

=I have several columns of data

I am trying to put a formula in A3 that will read say, =D5
When i drag this down on autofill it then reads =D6 (in cell A4), =D7(in
cell A5), =D8 (in cell A6) etc
Is it possible on autofill to get it to read =E5, =F5, =G5, =H5, =I5 etc as
i drag the formula down?

Thanks in advance!

Richard

Copy this to A3 then drag/copy down column A.

=INDEX($5:$5,ROWS($2:5))


Gord Dibben MS Excel MVP


=I have several columns of data

I am trying to put a formula in A3 that will read say, =D5
When i drag this down on autofill it then reads =D6 (in cell A4), =D7(in
cell A5), =D8 (in cell A6) etc
Is it possible on autofill to get it to read =E5, =F5, =G5, =H5, =I5 etc as
i drag the formula down?

Thanks in advance!

Richard