|If the incremented number of rows will always be the same, then I think the following solution will work. In fact, you will have some degree of latitude as to which rows you use as long as you can fit your desired rows within that incremented range. I know that doesn't make sense right now, but read on and it should become clearer.|
I'm going to start in B5 and use an increment of 30. I will copy my formula into B35, B65, and B95, returning the values in Sheet1 A1, A2, A3 and A4 respectively.
Obviously you may have to modify this to meet your exact needs.
The first thing we need is a Helper Column. A Helper Column is a column that is used to return an intermediate value that we will use in subsequent formulas. In my example, I will use Sheet2 Column A as my Helper Column.
In Sheet2!A5, enter a 1
In Sheet2!A6, enter this formula and drag it down as far as you need to:
This should result in a series of 1's in A5:A34, then 2's in A35:64, then 3's, etc. Basically the values in Column A will increment by 1 every 30 rows.
Now, in B5, enter this formula:
The INDIRECT function uses a text string as a cell reference, so B5 should return the value in SHEET1!A1 since there is a 1 in SHEET2!A5.
If you then copy that formula and paste it in B35, you will get:
Since SHEET2!A35 = 2, this formula will reference SHEET1!A2
In essence, whatever row you copy that formula into will return the value from Sheet1, Column A(whatever value is in that same row on Sheet2)
So, when I said you have some latitude as far as which rows you choose I meant that with an increment of e.g. 30, you can return the value from e.g. SHEET1!A2 in any cell from SHEET2!B35:B64.
It might be tough to find an increment that will work with B15, B25, B100 since B100 is so far from B25, but maybe you can find a formula for the Helper Column that produces a 1 in A15, a 2 in A25 and a 3 in A100. The concept of using INDIRECT would be the same, your helper formula would just be different than in my example.
Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.