SOLVED

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

@GettingThereSlowly 

=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.

indirect address.JPG

indirect sheet1.JPG

best response confirmed by GettingThereSlowly (New Contributor)
Solution

@GettingThereSlowly 

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)

 

@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.

 

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!