Every month I need to create charts that update the year-to-date information on a number of products. I have done this by using a pivot table and then manually creating a table of data for each product for a line-column chart. Can this be done more efficiently?
The chart needs all the months along the bottom as well as a YTD (year-to-date) value. The target is a line and the actuals are the columns.
Examples of the data with pivot and the manually created data for product A are below.
The chart needs all the months along the bottom as well as a YTD (year-to-date) value. The target is a line and the actuals are the columns.
Examples of the data with pivot and the manually created data for product A are below.
Book2 | ||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | |||
1 | DATE | TARGET | ACTUAL | MONTH | PRODUCT | PRODUCT | (All) | |||||
2 | 5-Jan-05 | 50 | 57 | Jan | B | |||||||
3 | 7-Jan-05 | 32 | 31 | Jan | E | Data | ||||||
4 | 20-Jan-05 | 50 | 51 | Jan | B | MONTH | Average of ACTUAL | Average of TARGET | ||||
5 | 22-Jan-05 | 43 | 40 | Jan | A | Jan | 45 | 44 | ||||
6 | 4-Feb-05 | 100 | 97 | Feb | C | Feb | 67 | 61 | ||||
7 | 4-Feb-05 | 32 | 55 | Feb | E | Mar | 71 | 68 | ||||
8 | 9-Feb-05 | 50 | 48 | Feb | B | Grand Total | 60 | 57 | ||||
9 | 15-Mar-05 | 80 | 80 | Mar | D | |||||||
10 | 16-Mar-05 | 43 | 49 | Mar | A | |||||||
11 | 22-Mar-05 | 100 | 101 | Mar | C | |||||||
12 | 23-Mar-05 | 50 | 55 | Mar | B | |||||||
Sheet1 |
Book3 | ||||||
---|---|---|---|---|---|---|
A | B | C | D | |||
1 | Month | Actual | Target | |||
2 | Jan | 40 | 43 | |||
3 | Feb | 43 | ||||
4 | Mar | 49 | 43 | |||
5 | Apr | 43 | ||||
6 | May | 43 | ||||
7 | Jun | 43 | ||||
8 | Jul | 43 | ||||
9 | Aug | 43 | ||||
10 | Sep | 43 | ||||
11 | Oct | 43 | ||||
12 | Nov | 43 | ||||
13 | Dec | 43 | ||||
14 | YTD | 45 | 43 | |||
Sheet1 |