Dragging formulas down a column

[mneghassi]

When dragging, I want the reference cell to add 57 rows each time. For instance, if I were to use the OFFSET function. The first use of the OFFSET function references B5. The next row down, I want it to reference B62 and so on. See below.

=OFFSET(B5,3,6)
=OFFSET(B62,3,6)
=OFFSET(B119,3,6)
=OFFSET(B176,3,6)

Is there a way to do that?

 

[Rick Rothstein]

Re: Dragging formulas down a column (this may be a super easy question, but I really don't know the anwser)

Give this formula a try...

=INDEX(B:B,57*ROWS($1:1)-52)

 

[mneghassi]

Re: Dragging formulas down a column (this may be a super easy question, but I really don't know the anwser)

Give this formula a try...

=INDEX(B:B,57*ROWS($1:1)-52)

Thanks so much. It works, but you probably knew that already

How does this formula work?

 

[mneghassi]

Re: Dragging formulas down a column (this may be a super easy question, but I really don't know the anwser)

Give this formula a try...

=INDEX(B:B,57*ROWS($1:1)-52)

I have the same set up, but instead of 57 rows, it's 85 rows. I tried changing the 57 to 85 and it definitely doesn't work. Any idea?

I just enrolled in an Excel course starting in a few weeks. Way too many holes in my Excel "skills"?

 

[Rick Rothstein]

Re: Dragging formulas down a column (this may be a super easy question, but I really don't know the anwser)

I have the same set up, but instead of 57 rows, it's 85 rows. I tried changing the 57 to 85 and it definitely doesn't work. Any idea?

I just enrolled in an Excel course starting in a few weeks. Way too many holes in my Excel "skills"?

You have to change two numbers, not just one. The OP for this thread said the first cell he wanted to reference started on Row 5.... 57 minus 52 (the two numbers in my formula) equals 5 (the OP's starting row number reference). So, if you first row is 5 like the OP's (you said you have the same set up), then the 57 would change to 85 and the second number to 80 (the difference is the starting row number reference). So my formula would become this for what I think your requirements are...

=INDEX(B:B,85*ROWS($1:1)-80)