View original (French)

Create a formula with column offset

Solved
Armin -  
michel_m Posted messages 18903 Registration date   Status Contributor Last intervention   -
Hello,

I'm not an Excel professional, but even after persevering and searching on different forums, I can't find a clear answer to my problem.
I have several values on the same row, but shifted by 7 columns.
I would like to duplicate them in a table below, in the same column, one below the other.
I used the formula =C3 for the 1st value,
then =OFFSET(C3,0,7) for the 2nd,
but I can't drag it down...

Is it possible with a "simple" Excel formula?

Armin

2 answers

Answer 1 / 2
michel_m Posted messages 18903 Registration date   Status Contributor Last intervention   3 320
 
Hello

if you start in line 3 for example
=OFFSET($C$3;0;7*(ROW()-3))
and drag down

if you start in line 5
=OFFSET($C$3;0;7*(ROW()-5))

--
Michel
0
Armin
 
THANK YOU VERY MUCH michel_m, your formula fits my needs perfectly!
0
Armin
 
And what if I want to drag the formula to the right?? (small addition)
0
michel_m Posted messages 18903 Registration date   Status Contributor Last intervention   3 320
 
By searching a bit, you could have found....

starting in column C (C--->3)
=OFFSET($C$3:7*(COLUMN()-3);0)
and drag down
0
Armin
 
It's not for lack of trying to tinker with this formula that I know little about, but it's not working.
C3 is indeed the cell of the first value to retrieve. The following ones are on the same line but offset by 7 columns.
I want to display my table starting from E10 and pulling to the right.
0
michel_m Posted messages 18903 Registration date   Status Contributor Last intervention   3 320 > Armin
 
=OFFSET($C$3,0,(COLUMN()-5)*7)
0