Unable to drag the formulaes

**Rajkumar** · 04-11-2009, 06:43 AM

Hi,

I have data from F1 to AG1...I am unable to create a formulae & drag in excel in a column like
=F1
=G1
=H1.....If i drag after H1...it is reflecting as
=F4
=G4
=H4.

Any idea..why this is occuring...& why not I1,J1......

raj

**Andy Pope** · 04-11-2009, 06:57 AM

Because you are dragging down and the formula will increare the row number rather than the column letter.

Try this as 1 way of doing it.
switch to R1C1 references via Tools > Options > General > R1C1 reference

In the first cell enter R1C1
Drag down and the C1 should increment to C2 C3 etc.
Select the cells and do a Find/Replace
R1C
=R1C

This will convert the text to actual references.
Now switch back to A1 notation

Or with formula

Starting in A4 use this formula. Drag down as required

=INDEX($1:$1,1,ROW()-ROW($A$3))

**Rajkumar** · 04-12-2009, 03:54 AM

Thanks,

=INDEX($1:$1,1,ROW()-ROW($A$3))
This formulae has helped to solve my problem,cause sometimes i need to insert columns.

Now suppose i need to copy down the values in row 5 to column b,what formuale i need to use.i tried with below formulae but not successful.

=INDEX($5:$5,5,ROW()-ROW($b$3))

Raj