12 Month Rolling Time Period

[SCADA]

I have two worksheets in a workbook. Worksheet 1 is a data tabulation worksheet and at the end of each month is filled with data values and date as shown below. Worksheet 1 will always continue to fill in to the right with each new month and data value. Worksheet 2 has a table with 12 columns as I need this table to be a 12 month rolling period from the current month to the past 11 months. What I would like the table to do is look at the data in Worksheet 1 and then populate the table on Worksheet 2 such that the table contains only the data from Worksheet 1 Columns from the current month plus the past 11 months. For instance when the file runs on March 1, 2018 the table on Worksheet 2 would examine Worksheet 1 and be filled in with the vales from February 2018 to March 2017.

I'm not quite sure if this requires a macro or just some functions, but any help/guidance/ideas/suggestions would be appreciated.

Worksheet 1:

1/31/2018	2/28/2018	etc...
19	6

<tbody>
</tbody>

Worksheet 2:

3/17	4/17	5/17	6/17	7/17	8/17	9/17	10/17	11/17	12/17	1/18	2/18
										19	6

<tbody>
</tbody>

 

[jtakw]

Hi,

Your description and your Sample does not match, this is based on your description, adjust formula with your sheet names and data range reference:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	1/31/2017	2/28/2017	3/31/2017	4/30/2017	5/31/2017	6/30/2017	7/31/2017	8/31/2017	9/30/2017	10/31/2017	11/30/2017	12/31/2017	1/31/2018	2/28/2018	3/31/2018
2	19	6	7	8	9	11	12	13	14	15	16	17	20	21	22
3
4	Feb-17	Mar-17	Apr-17	May-17	Jun-17	Jul-17	Aug-17	Sep-17	Oct-17	Nov-17	Dec-17	Jan-18	Feb-18	Mar-18	Apr-18
5	19	25	32	40	49	60	72	85	99	114	130	147	148	163	178
Sheet24

Cell Formulas
Range	Formula
A5	=SUMIFS($A2:$O2,$A1:$O1,">="&EDATE(A4,-12),$A1:$O1,"<"&A4)

Formula copied across.

 

[SCADA]

I apologize for my lack of detail in my original description. I will try another attempt at explaining what I am trying to do.

12/31/2016	1/31/2017	2/28/2017	3/31/2017	4/30/2017	5/30/2017	6/30/2017	7/31/2017	8/30/2017	9/30/2017	10/31/2017	11/30/2017	12/31/2017	1/31/2018	2/28/2018
5	0	7	1	5	0	7	0	4	1	5	0	7	0	4
4	1	4	2	4	1	4	0	5	2	4	1	4	0	5
3	0	8	3	3	0	8	0	6	3	3	0	8	0	6
2	2	4	4	2	2	4	0	3	4	2	2	4	0	3
1	0	5	5	1	0	5	1	6	5	1	0	5	1	6

<colgroup><col span="8"><col span="3"><col span="3"><col></colgroup><tbody>
</tbody>

So here is my table of Data. As you can see I have more than the past 12 months of data.

2017	2017	2017	2017	2017	2017	2017	2017	2017	2017	2018	2018
March	April	May	June	July	August	September	October	November	December	January	February

<colgroup><col span="5"><col span="3"><col span="3"><col></colgroup><tbody>
</tbody>

Here is the table I would like to populate. Forgive me for not pasting them as you have, I am new to this site and not sure how to past things in a table like format like you have. The code for each of the columns in the table is like:

=EDATE(TODAY(),-1)
=EDATE(TODAY(),-2)
=EDATE(TODAY(),-3)

So on and so forth for 12 months. and then the cells are formatted to display only Month and Year.

My question is then how do I evaluate all of the data at the top, in this case 15 months of data, and have the table sum all of the values below the date if it corresponds to the table's EDATE equation? My example final product would look something like:

2017	2017	2017	2017	2017	2017	2017	2017	2017	2017	2018	2018
March	April	May	June	July	August	September	October	November	December	January	February
15	15	3	28	1	24	15	15	3	28	1	24

<colgroup><col span="5"><col span="3"><col span="3"><col></colgroup><tbody>
</tbody>

I only want the sums of the most recent 12 months.

 

[jtakw]

Your 1st Table (Data Table) in Post #3 , for the month of May and August, instead of End of the month, you have the 30th, I assume that's a typo, since All other months shows last day of the month:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	12/31/2016	1/31/2017	2/28/2017	3/31/2017	4/30/2017	5/31/2017	6/30/2017	7/31/2017	8/31/2017	9/30/2017	10/31/2017	11/30/2017	12/31/2017	1/31/2018	2/28/2018
2	5	0	7	1	5	0	7	0	4	1	5	0	7	0	4
3	4	1	4	2	4	1	4	0	5	2	4	1	4	0	5
4	3	0	8	3	3	0	8	0	6	3	3	0	8	0	6
5	2	2	4	4	2	2	4	0	3	4	2	2	4	0	3
6	1	0	5	5	1	0	5	1	6	5	1	0	5	1	6
7
8	Mar-17	Apr-17	May-17	Jun-17	Jul-17	Aug-17	Sep-17	Oct-17	Nov-17	Dec-17	Jan-18	Feb-18
9	15	15	3	28	1	24	15	15	3	28	1	24
Sheet24

Cell Formulas
Range	Formula
A9	=SUMPRODUCT(($A1:$O1=EOMONTH(A8,0))*($A2:$O6))

A9 formula copied across.

 

[SCADA]

Thank you, this is exactly what I was looking for.

 

[jtakw]

You're welcome, welcome to the forum.