Referencing cell from distance/numerical step

**Johnston81** · 04-15-2010, 11:52 AM

Hi People!

First I apologize if this question has alreasy been asked and answered but today I've had trouble searching and even browsing the page; keeps on telling me I need to wait 30 seconds before performing a new search and only allows one search criteria. Nevermind.

I find my question is a little difficult to explain so I'll try with an example.

I have a value in a variable start cell, i.e.cell 'Sheet1'!O152, I know that the next desired value is a 144 cells below. I do not really have the option of 'dragging' down; I work with a large table/databse and only have the option of dragging across. What I want is something like:

With a step of 144 cells and a start cell like 'Sheet1'!O152

='Sheet1'!O296
='Sheet1'!O440

I've, naively, tried
='Sheet1'!O(B7+B8*E4)
Where B7 is the start cell in column O, B8 being the number of the next cell and E4 being the scale, i.e 2nd step E4 = 2, 3rd step E4 0 3.

I really hope somebody knows how to do this. It would save me hours of slave labour.

Thanks,
Johnston81

**NBVC** · 04-15-2010, 12:05 PM

Something like:

=INDEX('Sheet1'!O$152:'Sheet1'!O$1000,144*(ROWS($A$1:A1)-1)+1) copied down

where O$1000 represents last row in your data column. adjust as necessary.

**Johnston81** · 04-15-2010, 02:05 PM

NBVC. Thank you for your fast reply. I'm having trouble dragging this across, which is quite important. Could be because I don't quite get the ROWS($A$1:A1)-1)+1 part. Anyway I found, and used, an answer from another thread (embarresed to say) called 'Referencing every Nth Row from another worksheet'. It can be found on the path:

http://www.excelforum.com/excel-gene...worksheet.html

So, happy, I'm gonna put this thread down as Solved.
Thank you NBVC.

PS. Could you drag yours across? My final solution
=IF(INDEX('Sheet1'!$1:$65536; $B11+$B8*G$4;$K$1) = ""; "ERROR"; INDEX('Sheet1'!$1:$65536; $B11+$B8*G$4; $K$1))
Where B11 is start step (i.e. 152), B8 is step length (i.e. 144), G4 is step # (1,2, ... , N), these three calculating the row number and K1 is the column number. This, with slight adjustments in the mentioned variables, can be dragged across and also has the courtesy to tell if something goes wrong.

**NBVC** · 04-15-2010, 02:20 PM

If you copy across the only thing that changes is the G4.. which changes to H4, I4, etc..

Are you change the stepping as you go across, from 144 to 288, etc.?

Mine, copied across changes the reference column of O to P, Q, etc... The Rows() is there because you are copying down and retrieving data from the step amount below in increments.