CODEWITHSUNDEEP
excelvba
jhonty
jhonty

Reputation: 25

Reference a cell of other worksheet while incrementing the Row number by 15 every time

I am trying to get value of sheet 1 cell "A19" in cell "A1" of sheet 2 and sheet 1 cell "A36" (which is 19+15) in cell "A2" of sheet 2. Do not want to use VBA as the number of rows in the sheets is high. have tried indirect function but havent been able to figure out how to reference cell from other workbook. is there any way this could be done?

Upvotes: 1

Views: 158

Answers (2)

Steven Martin
Steven Martin

Reputation: 3282

As you have said, you can also use INDIRECT , but INDIRECT is volatile so INDEX is a better choice

=INDIRECT("Sheet1!A"&1+(ROW()-1)*2)

Upvotes: 1

Gary's Student
Gary's Student

Reputation: 96773

In Sheet2, cell A1 enter:

=INDEX(Sheet1!A:A,19+(ROWS($1:1)-1)*17)

and copy down.

This will retrieve the data from these rows:

19
36
53
70
87
104
121
138
155
172
189
206

That is because the increment between 19 and 36 is 17.
If you really want the increment to be 15, then substitute 15 in the top equation before the copy-down.

Upvotes: 2

Related Questions