Copy values for every nth cell

[dpgreen]

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

I have a column with dates running down it. I need a column that wil
collect every third month and copy it into a column next to it
Effectively I need a column (without spaces) next to the date colum
that will show quarterly dates. I need this formula to be automated fo
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 an
questions you need.

Thanks for your help in advanc

 

[Spencer101]

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

I have a column with dates running down it. I need a column that wil
collect every third month and copy it into a column next to it
Effectively I need a column (without spaces) next to the date colum
that will show quarterly dates. I need this formula to be automated fo
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 an
questions you need.

Thanks for your help in advance

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

 

[dpgreen]

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'
working with. I'm sure there is a fairly simple solution but excel can'
seem to recognize the pattern so I'm having to do it manually for ove
300 workbooks. Plus these workbooks are update automatically each mont
so I would like for the formula to be able to account for that.

Thanks,

Da

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

 

[Spencer101]

Hi Spencer,

Thanks for getting back to me, I've attached an example of what I'
working with. I'm sure there is a fairly simple solution but excel can'
seem to recognize the pattern so I'm having to do it manually for ove
300 workbooks. Plus these workbooks are update automatically each mont
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 value
already in columns E & F, or am I completely missing the idea here

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

 

[Kevin@Radstock]

Hi

Can you not use dynamic ranges.

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 o
stuff I was shown and that was one of them. I think it's something to d
with that fact that the percentages represent investment returns so a
the return increases so does the increase in the percentage, so a simpl
sum(...) does not add it up correctly in the context of the sum. If tha
makes sense...although that is just my assumption

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

 

[joeu2004]

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.

 

[joeu2004]

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]

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 previou

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 wer
unique
so that you could see that the formulas are "copying" the correc
values.

That's perfect, thanks for your hel

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