Forumla that can be dragged without intervention

Hi there. Try this and copy across.

=OFFSET(Sheet1!$BE$1,,(COLUMNS($A:A)-1)*3,,)

Here's another one...

=INDEX($BE1:$IV1,COLUMNS($A1:A1)*4-3)

Adjust for the correct end of range.

Glad to have helped! If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. It'd also be appreciated if you were to click the add Reputation button at the foot of any of the posts of those who helped you reach a solution.

Thanks!!

G.

You're welcome. We appreciate the feedback!

Just change COLUMNS($A:A) to ROWS($1:1)

Hi there. One comma missing:
=OFFSET(Sheet1!$BE$1,,(ROWS($1:1)-1)*3,,)

See post 13 above and try

=OFFSET(Summary!$BE$16,,(ROWS($5:5)-1)*3,,) but it returns a zero. What am I doing wrong please?

Ahh. Sorry!!!

=OFFSET(Summary!$BE$16,,(ROWS($1:1)-1)*3,,)

The ROWS bit is simply a counter to return 0,3,6,9,12, etc as the formula is dragged down. If you start with 5:5 the first value returned will be 12 cells to the right of BE16...

Originally Posted by Glen

The ROWS bit is simply a counter to return 0,3,6,9,12, etc as the formula is dragged down. If you start with 5:5 the first value returned will be 12 cells to the right of BE16...

No, it doesn't matter whether you start with ROWS($1:1) or ROWS($5:5)
ROWS counts the number of rows in your range. Between 1 and 1 there is 1 row, between 5 and 5 there is 1 row. The reason many people like to use ROWS as a counter instead of ROW() (which returns the row number) is for this very reason. If you insert or remove rows from your spreadsheet, the ROWS formula still returns the same value.

You're correct. I must have my sleepy head on today...

Originally Posted by ChemistB

No, it doesn't matter whether you start with ROWS($1:1) or ROWS($5:5)
ROWS counts the number of rows in your range. Between 1 and 1 there is 1 row, between 5 and 5 there is 1 row. The reason many people like to use ROWS as a counter instead of ROW() (which returns the row number) is for this very reason. If you insert or remove rows from your spreadsheet, the ROWS formula still returns the same value.

Correct.

However, I think it's better (more intuitive) to use the address of the first cell the formula is entered into. For example, if the first formula is entered in B20 and copied down then use ROWS(B$20:B20).

Also, the INDEX version is a better choice as it is not volatile.

Try this...

Entered in A5 and copied down:

=INDEX(Modules!BE$2:IV$2,ROWS(A$5:A5)*3-3+1)

Adjust for the correct end of range where I use up to IV2.

The formula used to pull the correct info but it is now just repeating what is in BE2 and not pulling what is in BE2, BH2, BK2, BN2 etc

That makes it sound like calculation is set to manual.

Make sure calculation is set to automatic.

Got the Formulas tab>Calculation Options>Automatic

In a formula like:

=OFFSET(Modules!$BE$2,,(ROWS($5:5)-1)*3,,)

OFFSET has the syntax OFFSET(rows, columns, height,width). The first ,, means that there is no OFFSET applied to the row number and the last ,, means that htere is no change to the height or width.

This is just a counter. Type =(ROWS($5:5)-1)*3 into a cel and copy it down. You'll see that it increments in 3s from zero. 0, 3, 6, 9, etc. The counter is in the columns section of offset, so every row that you copy the formula down, the value returned is initially what is in BE2 and then from 3 columns to the right, 6 columns to the right, etc.