Unfortunately, there is no easy way. Two non-programmatic ways:
(1) Make 12 copies of the Jan sheet. Then, copy the data from each
real sheet onto one of the copies. Delete the real sheet and rename
the copy with the real name.
(2) Use a single set of charts that use named formulas as their source
(for how see the links from the Dynamic Charts page of my web site).
However, in your case, you will have to use the INDIRECT function so
that the named formula refers to correct range on the appropriate
worksheet.
And, of course, I suspect there are any number of programmatic
solutions that you could consider. Depending on your comfort level
with VBA...
One would be to turn on the macro recorder (Tools | Macro > Record new
macro...), make copies of the charts on one sheet, change the formulas
for one of the charts, and turn off the macro recorder. Now, modify
the XL-generated code to fix every chart on the worksheet, and then
adapt the code to operate for all months.
--
Regards,
Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
I have created a workbook wherein each month data is entered into a worksheet titled for that month. The last sheet in the workbook is a Year to Date sheet summarizing the data from all months entered. At the bottom of my January sheet I created 20 graphs. My idea was to copy the blank January into each of the other sheets and change the month title. As usual (I have done this many, many times) everything
was fine, all formulas transferred fine. This is the first time I have tried copying charts though and to my surprise all charts in every worksheet referenced the January worksheet. Is there any way to copy charts relative to the worksheet they are in rather than in absolute mode so I don't have to go into every chart on every worksheet and change the Jan!$B$1:$B$10 to Feb!$B$1:$B$10 Mar!,APR!,.....and so on?
