Thread: referencing patterned cells

Hello, I have information on 7 different sheets that I would like to take some cells from each sheet and paste on a seperate sheet (there is 68 cells of data from each sheet that I want). The problem is the data is in every 5th cell and I don't want the other 4 in between to be linked. ex:

1 56.78
2 junk
3 junk
4 junk
5 junk
6 38.57
7 junk
etc..

Originally Posted by ShawnC

Hello, I have information on 7 different sheets that I would like to take some cells from each sheet and paste on a seperate sheet (there is 68 cells of data from each sheet that I want). The problem is the data is in every 5th cell and I don't want the other 4 in between to be linked. ex:

1 56.78
2 junk
3 junk
4 junk
5 junk
6 38.57
7 junk
etc..

One way is to use a helper column alongside your data. So assuming the above starts in say A6, in B6 put

=IF(MOD(ROW()-6,5)=0,"Keep","Junk")

and copy down. Note the '-6' bit should be altered to another row number if you start at a different row. This marks every 5th row with the word 'Keep'. You can now sort your data along with column B, using B as the sort key. All the 'Keeps' will then be together and you can then select them in one go and copy and paste them.

HTH