Getting cells that arent in order to reference cells in order.

[NobleExcel]

I hope the title makes sense. So I have a ref list on another sheet that I have cells to refer to but my cells aren't in order for example my first cell that refers to the list is in B2 and the next one is in B18. I'm figuring it has something to do with the offset function but I cant figure it out. Below are pictures to help illustrate my issue. Thanks.

https://imgur.com/a/OXIsD

 

[Rickmaurinus]

Hi NobelExcel,

Great idea to use the offset function. If you have two sheets (Sheet1 and Sheet 2). Sheet 2 contains the data you want to refer to. The pattern is: First value in B2, second in B18, third in B34. So there is 16 rows in between, every time.

Then in Cell A2, of sheet1, you could use the formula:

=OFFSET(Sheet2!$B$2; (ROW(A2)-2) * 16;  0)

The part saying ROW(A2) -2, first calculates the row of the current calculation, and then removes 2. So the first cell is always B2 on sheet2. But on the next row, it calculates Row 3 -2 = 1. And multiplies this by 16. Meaning it offsets the reference cell with 16 rows, but 0 columns.

I hope it helps!

Rick