I need to copy a formula to the right and have the column in the formula
remain constant but the row increase by one for each column to the right I
go. For example if in cell A10 I enter =A1 then copy that to the cells to the
right, it makes cell B10 =B1 and I need it to =A2, C10 needs to =A3, etc.
Can someone help me understand how to do this? I tried =$a1 but that is
keeping the column and row constant.

One way to get it dynamically transposed ..

In A10:
=OFFSET($A$1,COLUMN(A1)-1,)
Copy A10 to I10
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"C.A.S." wrote:
I need to copy a formula to the right and have the column in the formula
remain constant but the row increase by one for each column to the right I
go. For example if in cell A10 I enter =A1 then copy that to the cells to the
right, it makes cell B10 =B1 and I need it to =A2, C10 needs to =A3, etc.
Can someone help me understand how to do this? I tried =$a1 but that is
keeping the column and row constant.

CAS,

If I understand you correctly, you want to transpose the *formula* i.e. you want to see the actual formula across, right?

I tried the following.

In A10, I entered =$A1 and dragged it down to A18. Then I highlighted A11:A18 and did copy. I positioned the cursor in B10 and dragged across row 10. Editpaste specialtranspose. While A11:A18 was still selected, I pressed the delete key.

Is this what you want?

Max,

I learned from your post. Thank you. Please feel free to correct me.

Epinn

"C.A.S." wrote in message ...
I need to copy a formula to the right and have the column in the formula
remain constant but the row increase by one for each column to the right I
go. For example if in cell A10 I enter =A1 then copy that to the cells to the
right, it makes cell B10 =B1 and I need it to =A2, C10 needs to =A3, etc.
Can someone help me understand how to do this? I tried =$a1 but that is
keeping the column and row constant.

Hi

One way to achieve your end result, but it will not show up with
formulae like =A1, =A2 etc,
would be to enter in A10
=INDEX($A:$A,COLUMN())
and drag across
--
Regards

Roger Govier


"C.A.S." wrote in message
...
I need to copy a formula to the right and have the column in the
formula
remain constant but the row increase by one for each column to the
right I
go. For example if in cell A10 I enter =A1 then copy that to the cells
to the
right, it makes cell B10 =B1 and I need it to =A2, C10 needs to =A3,
etc.
Can someone help me understand how to do this? I tried =$a1 but that
is
keeping the column and row constant.

Epinn wrote:
.. I positioned the cursor in B10 and dragged across row 10. Editpaste specialtranspose.

well.. I might just describe the above as:
Right-click on B10 Paste Special Transpose OK

essentially nothing to correct really, Epinn,
except to point out that your response and Roger's
carry the additional benefit of being non-volatile <g
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---