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
<tbody>
</tbody>
<tbody>
</tbody>
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
<tbody> </tbody> |
<tbody>
</tbody>