Copy excel formula to increment columns

[dlogic]

Hello,

i need help with copying excel formula across rows:

A B C D
1 =B1
2 =C1
3 =D1
4 =B2
5 =C2
6 =D2

and so on.

I need this without macros.

[AliGW]

Welcome to the forum.

What's the formula?

[dlogic]

Text formatting destroyed my example.

So
A1 needs to be = B1
A2 needs to be = C1
A3 needs to be = D1
A4 needs to be = B2
A5 needs to be = D2
A6 needs to be = C2
and so on for 10000 rows.

[AliGW]

... and what is the formula???

It's not quite clear what you want.

[dlogic]

Hmmm.

In field A1 i enter formula =B1
In filed A2 i enter formula =C1
In filed A3 i enter formula =D1
but when i try to drag down or copy this formula to A4, A5, A6 it looks like this:
A4 = B4 and i need it to be B2.

Attachment 707535


How to make this work?

[FlameRetired]

Your attachment does not work. Try attaching an actual Excel workbook. Instructions are in the 'gold' banner at the top of each page.

In the meantime this is just a guess. In cell A1 and filled down.

Please Login or Register  to view this content.

[MelissaBallard]

I believe you need =TRANSPOSE(array). In the array you would highlight the cells that are the constant. There is a video if you google "Top 15 Advanced Excel 2016 Tips and Tricks". Hope it helps!

[kvsrinivasamurthy]

In A1 then copy down

=INDEX($B$1:$D$100,1+INT((ROWS($A$1:$A1)-1)/3),1+MOD((ROWS($A$1:$A1)-1),3))

[wk9128]

You can also try another method
A1 cell formula , drag down

[Limor_OP]

---Deleted----

[Pete_UK]

Here's a non-volatile solution, which you can put in A1:

=INDEX(B:D,INT((ROWS($1:1)-1)/3)+1,MOD(ROWS($1:1)-1,3)+1)

then copy down as far as you need to.

Hope this helps.

Pete