View Single Post
  #17   Report Post  
Posted to microsoft.public.excel.worksheet.functions
joeu2004[_2_] joeu2004[_2_] is offline
external usenet poster
 
Posts: 829
Default Copy values for every nth cell

"dpgreen" wrote:
I have a column with dates running down it. I need a
column that will collect every third month and copy
it into a column next to it.


By "copy", I presume that you mean a formula. Suppose your original data
are in A2:A1000, and you want the "copy" to start in B2. Put the following
formula into B2 and copy down as needed:

=INDEX($A$2:$A$1000,3*ROWS($B$2:B2)-2)

If you want to set up one time so it covers new data, you can put the
following formula into B2 and copy down through B1000:

=IF(INDEX($A$2:$A$1000,3*ROWS($B$2:B2)-2)="","",
INDEX($A$2:$A$1000,3*ROWS($B$2:B2)-2))

Pay close attention to the use of absolute references (e.g. $A$2) and
relative references (e.g. B2).

Also, although OFFSET and INDIRECT might seem easier to use, they are
"volatile" functions. That causes those formulas and all dependent formulas
to be recalculated every time any cell in any worksheet is modified. INDEX
is more efficient.