change row reference when pasting formula

[Guest]

I wish to chabge the way in wich Excel updates the cell references when
dragging formulas at the moment if dragging down Excel does the following
A1,A2, A3 etc..
I would like Excel to do the following A1,B1,C1.

All help will be gratefully receieved!

 

[vezerid]

=OFFSET($A$1,0,ROW()-1)

HTH
Kostis Vezerides

 

[Roger Govier]

Hi

You cannot change the way that Excel works. You can amend formulae to
pick up data from other cells using Offset or Index.
In cell A2, enter
=INDEX(1:65536,1,ROW())
and copy down
This will pull values from B1, C1 etc. as you go down the column.

 

[Guest]

Hi,

Thanks for the help,

this formula works for cells B1 and C1 but any subsequent data returns a
zero, any ideas on how to correct this?

Many thanks

Paul

 

[Roger Govier]

Hi

My apologies, I forgot to make rows 1:65536 absolute

=INDEX($1:$65536,1,ROW())