How can I get Excel to copy every nth cell from one sheet onto another?

Question

My data starts in C9 and I want to reference every 18th row. For example (the referenced sheet is named Summer):

=+Summer!C9

=+Summer!C27

=+Summer!C45

How can I get excel to recognize this pattern so I don't have to type it over and over?

Thanks!

Answer 1

Not pretty, but you can do something like this:

=indirect("Summer!C" & (Row()*18-9))

Basically use the current row as a multiplier, and construct the formula.

Edit: Indirect is a function that allows you to use a cell reference you have constructed from a string. So we're actually trying to contruct:

=indirect("Summer!C9")

which evaluates the same as your original

=Summer!C9

So all we need to do is compute the number 9 from whatever we have handy. To find the right math to calculate the number you want, lets test this simpler version:

=(Row()-n+1)*18)-9)

This should give you a number. Replace n with the row number where you first formula appears. So if your first formula was actually on row 25, you'd instead use:

=(Row()-25+1)*18)-9)

Once you are sure the number is correct, then just tack on the

=indirect("Summer!C" &

portion and you should be good to go. Note I've also assumed that each formula is in the row below the previous one. If that's not the case, then you'd have to make other adjustments. But its all just math.