Help with Offset & dragging forumlas

[Nickwell24]

There are a few other threads like this already, hoping to gain a bit more clarification in explaining why it works, not just a forumlas. I do MUCH better when I understand how it works.

This is my goal - These are starting in cell A16 going down.
=‘Database'!B6
=‘Database'!B11
=‘Database'!B16
=‘Database'!B21

When dragging A16/A17 down I get thie

=‘Database'!B6
=‘Database'!B11
=‘Database'!B8
=‘Database'!B13

I know I need to use offset, something similar to this:

=(OFFSET(‘Database'!B6,(ROWS(A$1:A1)-1)*2,0))

but i'm trying to figure out how to do it. (ROWS(A$1:A1)-1)*2,0)) confuses me. I partially stole this from another post so I'm trying to figure out what I need to put to make this applicable to my sheet.

[oeldere]

Please Login or Register  to view this content.

(ROWS(A$1:A1) gives the result 1

this counts (1 - 1)= 0*2 => zero * 2 = zero

If you drag that formula down you will get the formula

(ROWS(A$1:A2)

This gives the result 2

etc. etc.

(try it in your sheet).

So it count (2-1) = 1 * 2 = 2

the 3rth line will give as result

(3-1) * 2 = 2 * 2 = 4.

Hope I explained it well enough.


In your example you want to make steps of 5 rows (instead of 2 rows).

So you have to multiply * 5.

[Jonmo1]

You probably just need to change the *2 to *5

[Nickwell24]

Thank you so much, you just saved me hours of frusteration trying to figure this out. Essentially it just makes it a counter. With your help I was able to get my forumla going correctly.

For anybody needing to skip 4 reference cells this worked for me.

=(OFFSET(‘Database'!B6,(ROWS(A$1:A1)-1)*4,0))

[benishiryo]

welcome to the forum, Nickwell24.
ROWS(A$1:A1) counts the number of rows in the given range. A1:A1 only contains 1 row. so it returns 1. because the starting row is fixed (A$1), copying down the formula makes it (A$1:A2) & that returns 2. from your eg, you need the rows to be incremental by 5, so you need:
=OFFSET(Database!$B$6,(ROWS(A$16:A16)-1)*5,0)
i prefer to make the rows where the formula is, so i used A16. so this is essentially saying:
from Database!$B$6, go down (1 row minus 1) times 5. 1 row minus 1 is 0. 0 * 5 is 0. so A16 gives me Database!$B$6.

when copied down, it would be
from Database!$B$6, go down (2 rows minus 1) times 5. 2 rows minus 1 is 1. 1 * 5 is 5. so A17 gives me from Database!$B$11
and so on

but here's an alternative since OFFSET can be volatile (you can google for this):
=INDEX(Database!B:B,6+(ROWS(A$16:A16)-1)*5)
it's similar, but since i'm starting from Database!B1, i need to add 6 rows in front

[MarvinP]

Hi Nick and welcome to the forum,

Try in A16 this formula and pull it down. See if that works for you.

="Database!B" & (ROW()-16)*5 +6