Reference Every Fourth Cell

[BrooksTech]

Ive checked a lot of solutions via web-searches, including Mr Excel using INDIRECT and other crazy long-formulas, but havent found anything SIMPLE addressing this:

I need to skip FOUR rows on a separate worksheet and display the contents of that cell. E.G. The contents in Sheet2 of C7, C11, C15 etc. on down directly below one another on the destination sheet. Lets say that Excel allowed this:

=Sheet2!C(5+$4)

the next copied cell on the next row below would automatically look like:
=Sheet2!C(9+$4)

Ok, we know thats not a valid function or syntax. It would be nice if it was cus it would be way too simple! Anyways, you get the drift. Now what would the proper function and syntax be?

 

[ChrisM]

Just use Offset and Row() functions together:

Per your sample, you want C7, C11, C15

Put this formula in row 1 of another sheet. Row 1 is key here:

=Offset(Sheet2!$C$7, (Row() *4)-4+7,0)

Yes, you can combine the -4 and +7, but it makes more sense to me to show the logic. Now just drag that formula down.

 

[Aladin Akyurek]

Ive checked a lot of solutions via web-searches, including Mr Excel using INDIRECT and other crazy long-formulas, but havent found anything SIMPLE addressing this:

I need to skip FOUR rows on a separate worksheet and display the contents of that cell. E.G. The contents in Sheet2 of C7, C11, C15 etc. on down directly below one another on the destination sheet. Lets say that Excel allowed this:

=Sheet2!C(5+$4)

the next copied cell on the next row below would automatically look like:
=Sheet2!C(9+$4)

Ok, we know thats not a valid function or syntax. It would be nice if it was cus it would be way too simple! Anyways, you get the drift. Now what would the proper function and syntax be?

Destination sheet, A2...

=INDEX(Sheet2!C$5:C$100,4*ROWS(A$2:A2)-3)

Adjust to suit...