Problem with copying repeating formulas

[Young Grasshopper]

Hi,

I have a problem that probably is a quick fix, but I can't seem to figure it out..
I want to drag and copy a formula down, but the reference cell needs to be the same in the first three lines, then move on to the next reference cells for the next three, and so on..

A simplified example with formula in cell underneath each other;
=A1
=A1
=A2
=A2

I want to select and drag these cells down and get; =A1,=A1,=A2,=A2,=A3,=A3,=A4,=A4, and so on.
Instead i get; =A1,=A1,=A2,=A2,=A5,=A5,=A6,=A6,=A9,=A9...

Would appreciate any help

 

[Fluff]

How about

=INDEX(A:A,INT((ROWS(A$1:A1)-1)/2)+1)

 

[Young Grasshopper]

Hi,

Sorry, that wouldn't quite work I think.
The actual formula in question is =IF(LEN(G11)=0,0,RAND())
So I want it to be like this;

=IF(LEN(G11)=0,0,RAND())
=IF(LEN(G11)=0,0,RAND())
=IF(LEN(G11)=0,0,RAND())
=IF(LEN(G12)=0,0,RAND())
=IF(LEN(G12)=0,0,RAND())
=IF(LEN(G12)=0,0,RAND())
=IF(LEN(G13)=0,0,RAND())
=IF(LEN(G13)=0,0,RAND())
=IF(LEN(G13)=0,0,RAND())

But the number generated still needs to be random for each row.
The easiest solution is of course to just copy them, but it's over 1000 rows, so it would be nice if I could just copy/drag it down and it would be correct.

 

[Fluff]

In that case you can use

=IF(LEN(INDEX(G:G,INT((ROWS(A$1:A1)-1)/3)+11))=0,0,RAND())

 

[Young Grasshopper]

Works like a dream, my man!

I also have this formula; =LOOKUP(1, 0/FREQUENCY(0, 1/(1+H1:V1)),G11:U11)&" "&"|"&" "&LOOKUP(1, 0/FREQUENCY(0, 1/(1+H2:V2)),G11:U11)&" "&"|"&" "&LOOKUP(1, 0/FREQUENCY(0, 1/(1+H3:V3)),G11:U11)
This formula shows a random value from G11:U11 as long as the corresponding helping row, H1:V1 etc, is bigger den 0.
(Random since the values in H1:V1 is randomly generated from earlier fromula; =IF(LEN(G11)=0,0,RAND()

And need this formula to go;
(Row1) =LOOKUP(1, 0/FREQUENCY(0, 1/(1+H1:V1)),G11:U11)&" "&"|"&" "&LOOKUP(1, 0/FREQUENCY(0, 1/(1+H2:V2)),G11:U11)&" "&"|"&" "&LOOKUP(1, 0/FREQUENCY(0, 1/(1+H3:V3)),G11:U11)

(Row2) =LOOKUP(1, 0/FREQUENCY(0, 1/(1+H4:V4)),G12:U12)&" "&"|"&" "&LOOKUP(1, 0/FREQUENCY(0, 1/(1+H5:V5)),G12:U12)&" "&"|"&" "&LOOKUP(1, 0/FREQUENCY(0, 1/(1+H6:V6)),G12:U12)

(Row3) =LOOKUP(1, 0/FREQUENCY(0, 1/(1+H7:V7)),G13:U13)&" "&"|"&" "&LOOKUP(1, 0/FREQUENCY(0, 1/(1+H8:V8)),G13:U13)&" "&"|"&" "&LOOKUP(1, 0/FREQUENCY(0, 1/(1+H9:V9)),G13:U13)

Would that be possible with somewhat of a similar solution?

 

[Fluff]

As this is now a completely different question, it needs a new thread. Thanks

 

[Young Grasshopper]

Alright, no worries.
Thank you for your help, and enjoy your day

 

[Fluff]

You're welcome & thanks for the feedback.