Lookup every 5th value in another sheet?

[zpbsfg]

Hello-

I have 2 different sheets in a workbook; one contains stock values since the beginning of 2011 of a particular ETF. I'm trying to look at the performance of an ETF picking service that releases suggested ETFS each week. Therefore, I'm looking pull the high value of every 5th row. For example, the formula I'm inputting is:

=IVV!C41
=IVV!C46
=IVV!C51

However, whenever I try to auto-complete (drag and drop the corner), of these three highlighted values, excel grabs the value of a lower number, not picking up on the 'every 5' pattern.

How can I fix this?

Thanks

[tigeravatar]

zpbsfg,

Welcome to the forum!
Replace the range $C$41:$C$51 with your actual range:

Please Login or Register  to view this content.

[Jonmo1]

Try

=INDEX(IVV!C:C,(ROWS(A$1:A1)-1)*5+41)

DO NOT change teh A$1:A1 referece, it is not related to your data.
The *5 represents the interval..every 5 rows
The +41 represents the first row# to begin with.

[zpbsfg]

Thanks for your help Jonmo1 and tigeravatar!

[Jonmo1]

You're welcome.