Utilize data from multpile sheets into summary table and update charts "automatically"

[nshah1985]

Hello All,

First off, great website you all are so helpful, maybe one day I’ll know enough about Excel to be able to return the favor for some other n00b, lol.

Anyway, here’s the situation (sorry, I know this is a little long, but I wanted to do my best to explain in as much detail as I can):

Every month, a Summary worksheet is created (like the DataFormat example I am attaching to this post, this is a consistent format every month, so the cells are consistently located). There are/will be plenty of worksheets like that in this workbook (monthly data from 2010 until now, and adding a worksheet every month). I don't have too much input on the format of this, it's just how the raw numbers are published to us.

I would like to create a couple of auto-updating tables and a number of charts… I have been manually doing this but would like this task to be “automated” if possible (as little interaction needed as possible once the new month’s data has been added as a new worksheet).

A rough copy of the summary tables are included on the DataFormat worksheet (Attached) BUT in reality they’re the 1st tab of the worksheet (I just combined it here to show the data relationships and for ease of viewing).

SO specifically, this is what I’m trying to do:

At the beginning of every month, I will pull the number for the previous month, and paste them into this workbook as a new worksheet, which lets say is named May2013data . Every month can be like this (ex name them June2013data, July2013data, etc). Once that has been pasted into a new worksheet, I would like the data tables to expand to include the new month, and pull the data/totals as indicated. After that, I would like the data charted/updated the following ways:

Chart1 = trailing 12month plot of SummaryTable1 as a 2D line chart, x-axis is month/year, each group is its own plot on the chart, totals not included
Chart2 = Same as chart 1, but trailing 3month plot
Chart3 = column chart of Fiscal year totals (including FYTD) (*Complicating factor: Fiscal Year starts july 1st, so FY13 is 7/1/12-6/30/13)
Chart4 = 2D clustered column chart, month summary (ex: 1st cluster shows Jan. 2010, 2011, 2012, 2013 2nd cluster shows feb 2010, feb2011, feb 2012, etc)…
Chart 5 = trailing 12month plot of SummaryTable2 as a 2D line chart, x-axis is month/year, each TYPE is its own plot on the chart, totals not included

_______-
Again, sorry for making this so long just wanted to explain as much as I could to minimize confusion.

Thanks for any help you can provide. This will be done on a couple of Windows 7/ Excel 2010 computers. I have an ok/average Excel knowledge, but am pretty clueless when it comes to developing my own VBAs.

Thanks Again!


Here's my apreadsheet via MrExcelHTML
Excel 2010

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Data Source: Program 1(05-01-2013 To 05-31-2013)
2	Group	Product	Produced	Sold	Unsold	Group	Product	Produced	Sold	Unsold
3			Count	Count	Count			Count	Count	Count
4	Group1	Type1	3557	3673	131	Group5	Type1	1549	1541	109
5		Type2	178	150	19		Type2	98	97	6
6		Type3	393	391	48		Type3	458	463	17
7		Type4	29	32	0		Type4	278	279	12
8		Type5	96	96	5		Type5	133	127	7
9		Sub Total	4253	4342	203		Sub Total	2516	2507	151
10	Group2	Type1	1978	2077	25	Group6	Type1	143	140	19
11		Type2	143	140	8		Type2	9	9	1
12		Type3	858	879	39		Type3	79	75	6
13		Type4	286	275	11		Type4	15	16	0		Summary Table1
14		Type5	277	270	20		Type5	25	28	1			Apr-13	May-13	Jun-13
15		Sub Total	3542	3641	103		Sub Total	271	268	27		Group5SoldTotal		2507
16	Group3	Type1	267	292	15	Group7	Type1	215	203	15		Group6SoldTotal		268
17		Type2	21	23	2		Type2	23	22	0		Group7SoldTotal		452
18		Type3	63	65	0		Type3	86	88	1		Group3SoldTotal		498
19		Type4	76	78	1		Type4	117	117	0		GroupNameA Totals		3725
20		Type5	39	40	0		Type5	22	22	0
21		Sub Total	466	498	18		Sub Total	463	452	16		FiscalYearSummary		**FiscalYear starts July1st**
22	Group4	Type7	1	1	0								FY11	FY12	FY13
23		Type8	3	3	0	Sub Total of Above	Type1	1907	1884	143		Group5SoldTotal
24		Sub Total	4	4	0		Type2	130	128	7		Group6SoldTotal
25							Type3	623	626	24		Group7SoldTotal
26	Sub Total of Above	Type1	5803	6043	171		Type4	410	412	12		Group3SoldTotal
27		Type2	342	313	29		Type5	180	177	8
28		Type3	1314	1335	87		Typ6	0	0	0		Summary Table2
29		Type4	391	385	12								Apr-13	May-13	Jun-13
30		Type5	415	409	25	Total of Overall	Type1	7710	7927	314		(Grps1-4)Type1SoldTotal		6043
31		Type6	0	0	0		Type2	472	441	36		(Grps1-4)Type2SoldTotal		313
32							Type3	1937	1961	111		(Grps1-4)Type3SoldTotal		1335
33							Type4	801	797	24		(Grps1-4)Type4SoldTotal		385
34							Type5	595	586	33		GroupNAmeB Totals		8076
35							Type6	0	0	0
36						Grand Total		11515	11712	518
37

<tbody>
</tbody>

May2013​

Worksheet Formulas Cell Formula N19 =SUM(N15:N18) N34 =SUM(N30:N33) <tbody> </tbody>

<tbody>
</tbody>