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
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:
Thanks, guys. I'll try those out and see what happens.
Dave
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
Don’t have an account yet? Register yourself now and be a part of our community!