Reading values from another worksheet with offset

[vthokienj]

On mainSheet, I want to row by row show the values on otherSheet, which have a few blank rows between each. Seems like it should be simple enough, but getting the correct formula into the green cells of column B on mainSheet is proving difficult. I've tried too many combinations of offset/indirect/address functions and just don't see how to simply point to a starting cell and then increment the appropriate number of rows. Thanks for any help in defeating my arch nemesis, the offset function.

 

[6StringJazzer]

Does your image faithfully show what your data looks like? Does the data start in row 1? Is the number of blank rows between values always 2 rows?

I wouldn't use OFFSET for this because it's volatile, and doesn't give a particular advantage in this case. Put this in B2 and copy down:

=INDEX(otherSheet!$A:$A,(ROW()-2)*3+1)

$scratch.xlsm
	A	B
1	111
2		111
3		222
4	222	333
5		444
6
7	333
8
9
10	444
Sheet5

Cell Formulas
Range	Formula
B2:B5	B2	=INDEX($A:$A,(ROW()-2)*3+1)

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[vthokienj]

Does your image faithfully show what your data looks like? Does the data start in row 1? Is the number of blank rows between values always 2 rows?

I wouldn't use OFFSET for this because it's volatile, and doesn't give a particular advantage in this case. Put this in B2 and copy down:

=INDEX(otherSheet!$A:$A,(ROW()-2)*3+1)

$scratch.xlsm
	A	B
1	111
2		111
3		222
4	222	333
5		444
6
7	333
8
9
10	444
Sheet5

Cell Formulas
Range	Formula
B2:B5	B2	=INDEX($A:$A,(ROW()-2)*3+1)

No is the answer to your questions, but from what you posted I could figure out how to modify and also now understand how this works. Much thanks for the quick response.

 

[6StringJazzer]

You're welcome. Glad I was able to offer something helpful.