I want to increment a cell reference of a formula

Question

I have a sheet with player names that change every 19 rows and I want to be able to quickly drag the row down and have it auto paste the formula with it adding 19 rows and pasting the data in for that row.

"Sheet 1"

B2 - =Sheet2!B2
B3 - =Sheet2!B21

and I want to drag the formula in B2 down as many times and it makes the formula essentially,

B29 - =Sheet2!B517

I've attached a sample sheet for reference.

https://docs.google.com/spreadsheets/d/1tyJfjO5tErGSPBQaLswagHCy8mhJf7m9QM7ZJyU2I68/edit?usp=sharing

Are there any formulas I can plug in or shortcuts to use and cut down the time by not having to manually add 19?

Thank you

Answer 1

Use the OFFSET function.

For your example, you could use

=OFFSET(Sheet2!$B$2,(ROW()-2)*19,0)

This will result in the cell references you want with the caveat that the formulas won't be as transparent as bare cell references.

If you want to customize this for other uses, just know that the ROW() function returns the row number of the cell that holds the formula. Because your first formula is in row 2, I used (ROW()-2) as the factor of 19 to ensure that there is no offset on the first formula (just Sheet2!$B$2). For the next row (3), the formula will return Sheet2!B2 offset by 1*19 rows. The next will be offset by 2*19 rows, etc.