Sep 11 2022 01:43 AM
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.
Sep 11 2022 02:34 AM
=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.
Sep 11 2022 02:44 AM
SolutionUsually 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)
Sep 11 2022 02:46 AM
@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.
Sep 11 2022 02:49 AM
Sep 11 2022 02:49 AM
Sep 11 2022 02:44 AM
SolutionUsually 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)