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

  1.
    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
    1. Armin
       
      THANK YOU VERY MUCH michel_m, your formula fits my needs perfectly!
      0
    2. Armin
       
      And what if I want to drag the formula to the right?? (small addition)
      0
    3. 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
    4. 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
      1. michel_m Posted messages 18903 Registration date   Status Contributor Last intervention   3 320 > Armin
         
        =OFFSET($C$3,0,(COLUMN()-5)*7)
        0