Thread: automatically update charts

Hi all. I have to do 3 set of charts monthly for 15 different depts. For example January has 3 sets of data and I have to create charts for 15 different depts. That's 45 charts in all for each month. I created the charts already. I have the data on one sheet and created the charts on different sheets and label it. My problem is I don't want to recreate the charts every month. If I change the data sheet or add data, the charts become distorted and I have to go through and select the data for each chart again. How can I change the data on the data sheet and have the charts update automatically?
Thanks

Hi

See if the attached file gives you some hints. I doubt that it comes close to matching your data structure, but you may be able to apply the principles.

There are 3 named ranges.
depts is a list of the departments. It is used in the drop down on sheet2.
dates is a dynamic list that takes the last 12 months of data from sheet1!A:A. Add extra data and it will automatically adjust
data is a dynamic name that is based on dates and gets the data for the relevant department.

If you make a change to the department then the graph will update for the most recent 12 months of history.

HTH

rylo

How did you do the drop-down menu for the different charts? It's really cool!

Hi

I created a defined name on sheet1!B1:P1 called depts, then used Data, Validation, List with a source of =depts to create the drop down.

HTH

rylo