Create a relative column formula

L

Luzma

I want to copy a formula that has a relative column and an absolute row A$3
but when I try to copy it down the column doesn't change it copies the same
A$3 and I want B$3 C$3 etc.. Can someone help me? Thanks!!
 
J

jamescox

Sounds like your problem is revealed in the phrase "whencopy it down".
If you are staying in the same column, Excel has no reason to change the
column part of your formula.

Instead, copy the formula to the right - which will cause Excel to
generate cell formulas like you want. Then copy the formulas and use
Paste Special Transpose to get them into a column...
 
L

Luke M

When you copy down, XL will only change the relative row (if possible). If
you want to copy down, but want to change columns, perhaps this will work:

=INDIRECT("R3C"&ROW(A1),FALSE)

This currently refers to A3. Copying down will change the row in formula to
A2, causing overall formula to reference B3.
 
B

Bernard Liengme

That is exactly how it should be. Since you are copying down a column, the
reference to A will not change
Workaround: drag to the left. So lets say we now have =A$3, =B$3, =C$3 ...
starting in B10
Copy all these formulas except the first, move to B11 and use Paste Special
with Transpose specified
Now you have what you want but will need to delete C10, D10.....
best wishes
 
Top