 Formula help

• I'm trying to come up with a formula that will only return the data from certained numbered rows. I have a data list that is miles long but I need to extract the data from every 4th row. Is there anyway of doing this?

Thanks,
Dave

• Thanks. Let me know if you come up with anything. I'm using an archaic version of EXcel if that helps. It's from Office 97.

Dave

• This should colour each 4th row.

Sub ColourLedger()
'
' ColourLedger Macro
' Macro recorded 11/10/2002 by Roy Cox
'

Dim lastrow, i As Integer
lastrow = Range("a1").End(xlDown).Row
For i = 4 To lastrow Step 2
Rows(i).Interior.ColorIndex = 24
Next i
End Sub

See attachment. Then take a look at Chip Pearson's site-
http://www.cpearson.com/excel/SortByColor.htm

Then copy & paste to a new sheet

• You can use this formula and you can adjust it to your needs.

=OFFSET(A\$1,(ROW()-1)*4,0) :lol:

• Dave,

for a more "generic" solution to all cases, try the following :

=INDIRECT("scource column letter"&(scource start row+(((ROW()-target start row)/target repeat rate)*scource repeat rate)))

The 5 variables are :

1) Scource cells' column reference
2) Scource cells' starting row
3) Scource cells repeat rate
ie if they are contiguous, the repeat rate is "1"
if they populate every other cell, the repeat rate is 2
etc etc
4) Target cells' starting row
5) Target cells repeat rate