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

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

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?

Yes that's it, well along with the value from column B as well. Effectively it is a table to show quarterly results, so return the values of cells B, E + F every 3 months with the intervals being every March, June, September and December.

Many thanks

Put the below formulas in these cells and copy down.

K6 - =VLOOKUP(J6,B:E,4,FALSE)
L6 - =VLOOKUP(J6,B:F,5,FALSE)

"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.