Copying cells 26 rows apart to create a summarty list in another sheet
Does anybody know how to copy only single cells that are 26 rows apart in one sheet to paste over and create a summary list in another sheet?
For example in Sheet2 I have a long list of data and I only want to pick out and copy across the cells that are in G4, G30, G56....and so on.
I will then paste these in a short list in Sheet1 starting in A1,A2,A3 etc to create basically a short summary list of the values of them specific cells.
Does anyone know if there is a simple formula for this in Excel 2010?
Or whether there is a simple way to do the first cell then drag it down to copy only them cells that are 26 rows apart?
So basically in my head the summary list formula would look something like this once you drag it down to copy:
A1 ='Sheet2!G4
A2 ='Sheet2!G30
A3 ='Sheet2!G56
and so on....
its doing my head in!
Any help appreciated!
in a free column, put this
in a free column, put this formula: =MOD(ROW()-1,26)+1
you will then have numbers 1 to 26 repeated... you can then filter on the number you want, then copy the data to a new sheet