As shown in the image below, what I need is to drag down the cell and get the values as shown in the yellow cells. The values will go into successive rows, but will be taken from every 4th column, from the same row as the value I drag down.

In this example, the cell I'm dragging references B1, so the cell below it should reference F1, the next cell J1, etc.

enter image description here

I've tried Googling a solution, and tried using OFFSET and INDIRECT functions without success.

  • So are you trying to add values or increment them? I think a more detailed explanation would be helpful to solve this problem. – Mr. Hargrove Sep 29 '16 at 17:48
  • Sorry if I wasn't detailed enough, what I wanted to do is what Scott Craner answered, thank you very much anyways! – Sulfito Sep 29 '16 at 19:38
  • Glad you got a solution! :) – Mr. Hargrove Sep 29 '16 at 19:43

You will want to use Index:

=INDEX($1:$1,((ROW(1:1)-1)*4)+ 2)

enter image description here

  • THANK YOU VERY MUCH Scott Craner! That was just what I wanted to do! :D Could you explain me how does it work? – Sulfito Sep 29 '16 at 19:40
  • As it is copied down Row(1:1) which returns 1 changes to Row(2:2) which is 2 and so forth. So the second par tof the index returns the index number. So Row(1:1) returns 2. So it returns the second cell in row 1. When it is draged/copied down the math resolves to 6 and so forth. – Scott Craner Sep 29 '16 at 21:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.