Autofill horizontally - Leaving empy cells at regular intervals

[mgris]

Hi everyone,


This is my first post here. What an awesome website !


I am sure that what I want is already somewhere in the forum.

But I don't really know how "phrase" what I am searching for...

So lets go to the point :


I have in the following cells : C1, D1, E1, F1 etc...

What I want is : E2=C1, H2=D1, K2=E1 etc...


Since there quite a bunch of data, doing it manually is not an option.


What would you recommend ?


Thx in advance,


M.

 

[Hui]

In E2: =IF(MOD(COLUMN(),3)=2,OFFSET($A$1,,INT(COLUMN()/3)+1),"")

Copy across

 

[mgris]

Hi Hui,


Thx a lot for your quick answer which was right to the point (I edited my previous stupid answer !!!)


Thx and thx again.


Have a great week end.


Cheers


M.

 

[mgris]

If you have some spare time, an little explanation of your formulas would be most welcome :-D

 

[Hui]

Mgris

=IF(MOD(COLUMN(),3)=2,OFFSET($A$1,,INT(COLUMN()/3)+1),"")

There are 2 main parts in the above


IF(MOD(COLUMN(),3)=2

This looks at the current cell and asks is the remainder of dividing the current Column by 3, 2?

The cells in Row 2 are offset by 3 and start at E Column 5.

That is when the current column / 3 has a remainder of 2 it will be offset from Column E by 3 spaces

When this is true the next bit happens


OFFSET($A$1,,INT(COLUMN()/3)+1)

When the first bit is true this happens


Offset looks at a Reference of A1 and offsets the location by INT(COLUMN()/3)+1)

That is it takes the integer of ( the current Column divided by 3) and adds 1 to it

This will increase by 1 for every 3 columns in Row 2

and it is offset by 1 as the first cell is D1 (Column 4) which has an integer /3 of 1

But we are offsetting from A1, not A0

so we need to add 2 to the Int(col/3) which starts at 1 to get to cell c1


I don't think I even understand that?


Just pick a few cells in Row 2 and work through the equation piece by piece

 

[mgris]

I just have (infinity) words to say :

Thank you very very very...(Infinity)... much !

Cheers,

M.