Forum Discussion
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.
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)
5 Replies
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!
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)- Oooo This one is very elegant I like this!
=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.
