Copy values for every nth cell

[dpgreen]

Hi all, been a long time viewer of the forum and felt it was finally
time I signed up, said hello, thanked you all and then asked you all for
help.

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.
Effectively I need a column (without spaces) next to the date column
that will show quarterly dates. I need this formula to be automated for
future drag and autofill features when newer dates are added in.

If i've not been clear with what I am looking for then please ask any
questions you need.

Thanks for your help in advance




--
dpgreen

[Spencer101]

Hi,

Do you mean you need it to sum all values that fall into each quarter?

Which version of Excel are you using?

Any chance of a sample workbook with dummy data?

S.




--
Spencer101

[joeu2004]

"dpgreen" <d...@thisemailisnotVALID.com> 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.

[dpgreen]

Spencer101;1607051 Wrote:
> Hi,
>
> Do you mean you need it to sum all values that fall into each quarter?
>
> Which version of Excel are you using?
>
> Any chance of a sample workbook with dummy data?
>
> S.

Hi Spencer,

Thanks for getting back to me, I've attached an example of what I'm
working with. I'm sure there is a fairly simple solution but excel can't
seem to recognize the pattern so I'm having to do it manually for over
300 workbooks. Plus these workbooks are update automatically each month
so I would like for the formula to be able to account for that.

Thanks,

Dan


+-------------------------------------------------------------------+
|Filename: Example 1.zip |
|Download: http://www.excelbanter.com/attachment.php?attachmentid=658|
+-------------------------------------------------------------------+



--
dpgreen

[Spencer101]

dpgreen;1607055 Wrote:
> Hi Spencer,
>
> Thanks for getting back to me, I've attached an example of what I'm
> working with. I'm sure there is a fairly simple solution but excel can't
> seem to recognize the pattern so I'm having to do it manually for over
> 300 workbooks. Plus these workbooks are update automatically each month
> so I would like for the formula to be able to account for that.
>
> Thanks,
>
> Dan

Do you want to recreate the smaller table (J6:L11) from the values
already in columns E & F, or am I completely missing the idea here?


+-------------------------------------------------------------------+
+-------------------------------------------------------------------+



--
Spencer101

[Kevin@Radstock]

Hi

Can you not use dynamic ranges.


dpgreen;1607067 Wrote:
> Not a problem, I'm not 100% sure why I use the product array formula,
> I'm fairly new to excel, only been using it about 6 months, and a lot of
> stuff I was shown and that was one of them. I think it's something to do
> with that fact that the percentages represent investment returns so as
> the return increases so does the increase in the percentage, so a simple
> sum(...) does not add it up correctly in the context of the sum. If that
> makes sense...although that is just my assumption.


+-------------------------------------------------------------------+
+-------------------------------------------------------------------+



--
Kevin@Radstock

[joeu2004]

"dpgreen" <d...@thisemailisnotVALID.com> wrote:
> I've attached an example of what I'm working with.

[....]
> |Download: http://www.excelbanter.com/attachment.php?attachmentid=658|

Sorry, I did not see this response until long after I posted my previous
response.

Adapting to your example, enter the following formulas as indicated, then
copy down through row 11:

J6: =INDEX(B$6:B$24,3*ROWS(J$6:J6)+1)
K6: =INDEX(E$6:E$24,3*ROWS(K$6:K6)+1)
L6: =INDEX(F$6:F$24,3*ROWS(L$6:L6)+1)

If you would like those formulas to work as add data following row 24, enter
the following as indicated, then copy down as many rows as you like:

J6: =IF(INDEX(B$6:B$24,3*ROWS(J$6:J6)+1)="","",
INDEX(B$6:B$24,3*ROWS(J$6:J6)+1))
K6: =IF(INDEX(E$6:E$24,3*ROWS(K$6:K6)+1),"","",
INDEX(E$6:E$24,3*ROWS(K$6:K6)+1))
L6: =IF(INDEX(F$6:F$24,3*ROWS(L$6:L6)+1)="","",
INDEX(F$6:F$24,3*ROWS(L$6:L6)+1))

Obviously, it would be better if the values in columns E and F were unique
so that you could see that the formulas are "copying" the correct values.

[dpgreen]

'joeu2004[_2_ Wrote:
> ;1607114']"dpgreen" <d...@thisemailisnotVALID.com> wrote:-
> > I've attached an example of what I'm working with.-
> [....]-
> > |Download:
> http://www.excelbanter.com/attachment.php?attachmentid=658|-

>
> Sorry, I did not see this response until long after I posted my previous
>
> response.
>
> Adapting to your example, enter the following formulas as indicated,
> then
> copy down through row 11:
>
> J6: =INDEX(B$6:B$24,3*ROWS(J$6:J6)+1)
> K6: =INDEX(E$6:E$24,3*ROWS(K$6:K6)+1)
> L6: =INDEX(F$6:F$24,3*ROWS(L$6:L6)+1)
>
> If you would like those formulas to work as add data following row 24,
> enter
> the following as indicated, then copy down as many rows as you like:
>
> J6: =IF(INDEX(B$6:B$24,3*ROWS(J$6:J6)+1)="","",
> INDEX(B$6:B$24,3*ROWS(J$6:J6)+1))
> K6: =IF(INDEX(E$6:E$24,3*ROWS(K$6:K6)+1),"","",
> INDEX(E$6:E$24,3*ROWS(K$6:K6)+1))
> L6: =IF(INDEX(F$6:F$24,3*ROWS(L$6:L6)+1)="","",
> INDEX(F$6:F$24,3*ROWS(L$6:L6)+1))
>
> Obviously, it would be better if the values in columns E and F were
> unique
> so that you could see that the formulas are "copying" the correct
> values.

That's perfect, thanks for your help


+-------------------------------------------------------------------+
+-------------------------------------------------------------------+



--
dpgreen