change row reference when pasting formula

S

Stratful

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!
 
R

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.
 
S

Stratful

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
 
R

Roger Govier

Hi

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

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