Skip rows when filling down

[banstyk]

I have information in a column (column W) and I need to take the information in every 6th row. I would like this information to populate in column Y. For example, I would like Y2 to show the information in W2, and then Y3 to show the information in W8, and Y4 to show the information in W14, and so on. If I attempt to fill down, it simply repeats the pattern rather than continuing to add 6 rows each time. I know there is a way to do this using the INDEX fundtion, but I am not sure of the details. Or perhaps there is a better way altogether?

 

[ChrisOswald]

Not necessarily a better way, but

=INDIRECT("W" &6*ROW()-10)

in Cell Y2
is how I generally handle this type of problem.

 

[banstyk]

Thanks so much! That works perfectly. But what does the ()-10) part refer to?

 

[ChrisOswald]

Row() returns the row number of the cell it's in. (see excel's help for the other ways the row function can be used).

you have Y and W' row numbers related as follows:
Y W
2 2
3 8
4 14

6 * y's rownumber - 10 = w's rownumber, which is what

6*row()-10 represents.