Hi all,
I have three charts that I have to do every month. Each set of charts have 15 dept that we have to do for each chart. I have the format for each chart and I have to create them each month. Is there any way to automate instead of creating 45 charts each month.

Hi,
Create one chart with your formatting, etc. Once you've done it go to it and right click.
Chose Chart Type. Click on the Custom Types tab and change the option in the Select From box to "User Defined".
Click on the Add button and add a title/description of your chart.
Now when you create charts 2 to 45 you can highlight the data for your chart wizard and pick your custom chart from Custom Types tab.

There are several ways to do this.

1. Formulas with data setup: keep all data in one worksheet/workbook, and then all setup worksheets separate, and then charts put on separate worksheets. By do it this way you only need to change one cell for each update (per workbook) and all charts automatically update instantly. I have done this with 100 cities, 27 different aspects for each, with rolling 13 week charts. After the data pull from the mainframe, I can update all 700 charts, which are automatically linked to Powerpoint, in less than 15 minutes. This has the advantage that you can choose any time period and changes are instantaneous.

2. Use VBA to recreate the charts automatically. This has some advantages, but assumes you know how to work with charts in VBA.

If you want to provide a sample of one, with dummy data, I can show you an example of #1.

Ok. I'll take you up on your offer so I can better understand. The following is a sample of my charts. Ok I just tried to upload the .xls file but apparently I can't for this file. How do I convert it?

I think I got it zipped.
thanks

Okay, a couple of questions, will the data always come in this format? Will you be adding rows or columns?

Also, how do you receive the data? In this format? Do you do anything with the data to get it into this format?

I assumed that you would additional monthly data below in the same columns. So I added three columns to your data worksheet (A, B, C). B is the date (always use the 1st for a month reference). C is the Group to distinguish the four different groups. A is a concatenate formula (to be used as a lookup reference).

Three dynamic named ranges on the worksheet:

RawData (data itself)

=OFFSET(RawDataApril07!$A$2,0,0,COUNTA(RawDataApri l07!$A:$A)-1,COUNTA(RawDataApril07!$1:$1))

DataLU (reference, Column A for lookup)

=OFFSET(RawDataApril07!$A$2,0,0,COUNTA(RawDataApri l07!$A:$A)-1,COUNTA(RawDataApril07!$1:$1))

DataHead (for headings lookup)

=RawDataApril07!$A$1:$G$1

Control worksheet has two defined names:

CurMonth refers to Cell $B$2

It uses a dropdown (Data > Validation > List)

=Months

Months is a dynamic named range

=OFFSET(Control!$K$3,0,0,COUNTA(Control!$K:$K)-1,1)

Worksheets D, E, F, and G are all identical, except each references a different group. They will not ever have to be updated or changed, once you have them set up like you want.

Each uses worksheet level defined names, i.e.

D!FigTitle (refers to cell $B$1)

Cell $B$5 holds the Group differentiator

Cell $A$8 has a formula

=CurMonth

This allows you to change the month in only one place (the dropdown in Control worksheet, cell B2).

The charts are all on the worksheet named Graphs. In column A, you can sse the worksheet that is referenced for each chart. Each chart covers exactly one cell (i.e. D4) [to get it precise, hold down the ALT key and resize the chart, it will snap to the cell borders); there is a formula in the cell just above it to link to the FigTitle of the appropriate linked chart, with any additional data:

=D!FigTitle&" ("&TEXT(CurMonth,"mmm/yy")&" Team "&D!$B$5&")"

PS: I changed the color scheme so that you have greater variation on the grays.

HTH

I should point out that you can just copy worksheets D, E, F, or G as many times as you want and rename; nothing has to be done, except to add additional charts on the Graphs worksheet, and change the references to the charts by changing just the worksheet name.

And the formulas for the data in each of those worksheets is an INDEX that uses match to find appropriate data. For instance, this is the formula from worksheet D, in cell D8

=INDEX(DataRaw,MATCH($A8&$B$5&D$6,DataLU,FALSE),MA TCH($B$6,DataHead,FALSE))

Then it is copied across for as many columns as you need.