Easy One - reference to non-sequential cells

Question

I'm sure this has been asked before, but I couldn't figure out the right search terms to get the answer I was seeking, so apologies...

I have one worksheet with regular but non-sequential data (i.e. values in A1, A11, A21...)

In another sheet, I want to be able to put in a formula to get the values from those cells, and have it be easily scalable so I can drag the formula down and continue to get every nth cell from Sheet1. It looks like OFFSET will work, but it's not that elegant. VBA seems like overkill...Is there a better way?

Thanks!

Are the data equally spaced? i.e. A1, A11, A21, A31, or could it be A1, A11, A17, A30, etc.? — Trevor D
– Trevor D, Commented Dec 16, 2014 at 14:52

Abe Gold · Accepted Answer · 2014-12-16 15:08:07Z

1

Either Offset like you said, or something like this would be on the other sheet in Row 1 and copied down (assuming the first sheet is called 'sheet1', or change to the actual name:

=INDIRECT(ADDRESS(ROW()*10-9,1,,,"sheet1"))

Offset would be even clearer:

=OFFSET(Sheet1!A1,((ROW())-1)*10-ROW()+1,0)

answered Dec 16, 2014 at 15:08

Abe Gold

2,38718 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Brandon Over a year ago

ahh, thanks! i was struggling trying to figure out how to implement the first solution, but the second one helped me figure out why offset wasn't working for me earlier.

Collectives™ on Stack Overflow

Easy One - reference to non-sequential cells

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related