Change the row-to-row increment value when copying down a column, from +1 to +10

New Contributor

OK, I'm learning about certain functions etc and I'm having a brain fart. I assume the ROW or INDEX will help me but I'm tired!


Essentially I have a Sheet which pulls data from other sheets to create a Summary. 

So I have a formula on sheet2 that is

     A1              ¦     B1

=Sheet1!A100 ¦  =Sheet1!G100     (etc)


What I want when the formula is copied down is for the next for to be :

A1                    ¦    B1

=Sheet1!A110 ¦  =Sheet1!G110   1etc


What I get under normal methods is this: 

A1                    ¦    B1

=Sheet1!A101 ¦  =Sheet1!G101


Can some explain in basic terms how I do this so I can build on this method for the entire sheet. Thanks in advance. 




5 Replies



You can try this formula in cell A1.


You can try this formula in cell B1.

indirect address.JPG

indirect sheet1.JPG

best response confirmed by GettingThereSlowly (New Contributor)


Usually there are more than one way to get to Rome. Here is another possibility with OFFSET():



@GettingThereSlowly Although you have already accepted a solution, try this in A1:


In B1, just update the column reference to G:G.


Thank you - always happy to get more its such a simple thing that im sure the different ways will have their usages!
Oooo This one is very elegant I like this!