SOLVED

New Contributor

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

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

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

``=INDIRECT(ADDRESS(100+((ROW(1:1)-ROW(\$1:\$1))*10),1,,,"sheet1"))``

You can try this formula in cell A1.

``=INDIRECT(ADDRESS(100+((ROW(1:1)-ROW(\$1:\$1))*10),7,,,"sheet1"))``

You can try this formula in cell B1.

best response confirmed by GettingThereSlowly (New Contributor)
Solution

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

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

``````A1=OFFSET(Sheet1!A\$90,ROW()*10,0)
resp.
G1=OFFSET(Sheet1!G\$90,ROW()*10,0)``````

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

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

``=INDEX(Sheet1!A:A,IF(ROW()=1,100,100+ROW()*10-10))``

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

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

Thank you - always happy to get more its such a simple thing that im sure the different ways will have their usages!

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

Oooo This one is very elegant I like this!