Hi,

I have workbook with about 50 sheets. they have exactly the same table ,
just data(numbers in the table) are different. Also created a chart
based on the table in the first worksheet. Chart is embedded in the
sheet). I spend some time to format the chart and want to copy and paste
it on the other sheets, but I want to reflect the data in each sheet. So
each sheet has its own graph, which looks similar but reflects data on
the sheet.
Problem I am having is when I copy/paste it it paste absolute reference
, so I have manually to fix data range and with that many sheets its
kinda cumbersome.. and this is not the only file
Any suggestions how I can copy my chart so it reflect the data on the
sheet that is copied to?

Thanks,
Sam

The easiest and least tedious way is to copy the original worksheet that
contains the chart. The copied chart thus links to the copied worksheet.
Now copy the data from the table on the second worksheet, and
paste-special-values or -formulas into the table on the copied worksheet.

Lather, rinse, repeat.

- Jon
-------
Jon Peltier
Peltier Technical Services, Inc.
774-275-0064
http://peltiertech.com/


On 3/4/2010 4:15 PM, Sam wrote:
> Hi,
>
> I have workbook with about 50 sheets. they have exactly the same table ,
> just data(numbers in the table) are different. Also created a chart
> based on the table in the first worksheet. Chart is embedded in the
> sheet). I spend some time to format the chart and want to copy and paste
> it on the other sheets, but I want to reflect the data in each sheet. So
> each sheet has its own graph, which looks similar but reflects data on
> the sheet.
> Problem I am having is when I copy/paste it it paste absolute reference
> , so I have manually to fix data range and with that many sheets its
> kinda cumbersome.. and this is not the only file
> Any suggestions how I can copy my chart so it reflect the data on the
> sheet that is copied to?
>
> Thanks,
> Sam

Still its tons of copy paste... i was trying to use
MID(CELL("filename"),FIND("]",CELL("filename"))+1,255)
to get the sheet name and then indirect formula to "glue" it to the range,
but when i put that in the series formula, it tells me its not valid
Is there any other way? Like Can it be done with VBA Code, like telling it
to loop through all sheets, and construct a graph based on sheets name and
specific range? (note the data range s same on every sheet - lets say axes
names are in A1:A100, and data is in B1:B100)I need simple line graph to
chart the trend and then apply a custom style i saved as template (working on
2007 or 2010beta)

Any suggestions will be appreciated




"Jon Peltier" wrote:

> The easiest and least tedious way is to copy the original worksheet that
> contains the chart. The copied chart thus links to the copied worksheet.
> Now copy the data from the table on the second worksheet, and
> paste-special-values or -formulas into the table on the copied worksheet.
>
> Lather, rinse, repeat.
>
> - Jon
> -------
> Jon Peltier
> Peltier Technical Services, Inc.
> 774-275-0064
> http://peltiertech.com/
>
>
> On 3/4/2010 4:15 PM, Sam wrote:
> > Hi,
> >
> > I have workbook with about 50 sheets. they have exactly the same table ,
> > just data(numbers in the table) are different. Also created a chart
> > based on the table in the first worksheet. Chart is embedded in the
> > sheet). I spend some time to format the chart and want to copy and paste
> > it on the other sheets, but I want to reflect the data in each sheet. So
> > each sheet has its own graph, which looks similar but reflects data on
> > the sheet.
> > Problem I am having is when I copy/paste it it paste absolute reference
> > , so I have manually to fix data range and with that many sheets its
> > kinda cumbersome.. and this is not the only file
> > Any suggestions how I can copy my chart so it reflect the data on the
> > sheet that is copied to?
> >
> > Thanks,
> > Sam
> .
>

You could paste the chart onto a different sheet, even though it links
to the original sheet. Then you can change the sheet name in the series
formulas.

Normally this is a pain to do, but I wrote a little utility that handles
it. I didn't mention it at first because sometimes Excel is funny about
editing sheet names in the series formula. But if your sheet names are
simple, it should work okay. Read about the utility here:

How to Edit Series Formulas » Peltier Tech Blog
http://peltiertech.com/WordPress/how...ries-formulas/

- Jon
-------
Jon Peltier
Peltier Technical Services, Inc.
http://peltiertech.com/


On 3/5/2010 12:28 PM, Sam wrote:
>
> Still its tons of copy paste... i was trying to use
> MID(CELL("filename"),FIND("]",CELL("filename"))+1,255)
> to get the sheet name and then indirect formula to "glue" it to the range,
> but when i put that in the series formula, it tells me its not valid
> Is there any other way? Like Can it be done with VBA Code, like telling it
> to loop through all sheets, and construct a graph based on sheets name and
> specific range? (note the data range s same on every sheet - lets say axes
> names are in A1:A100, and data is in B1:B100)I need simple line graph to
> chart the trend and then apply a custom style i saved as template (working on
> 2007 or 2010beta)
>
> Any suggestions will be appreciated
>
>
>
>
> "Jon Peltier" wrote:
>
>> The easiest and least tedious way is to copy the original worksheet that
>> contains the chart. The copied chart thus links to the copied worksheet.
>> Now copy the data from the table on the second worksheet, and
>> paste-special-values or -formulas into the table on the copied worksheet.
>>
>> Lather, rinse, repeat.
>>
>> - Jon
>> -------
>> Jon Peltier
>> Peltier Technical Services, Inc.
>> 774-275-0064
>> http://peltiertech.com/
>>
>>
>> On 3/4/2010 4:15 PM, Sam wrote:
>>> Hi,
>>>
>>> I have workbook with about 50 sheets. they have exactly the same table ,
>>> just data(numbers in the table) are different. Also created a chart
>>> based on the table in the first worksheet. Chart is embedded in the
>>> sheet). I spend some time to format the chart and want to copy and paste
>>> it on the other sheets, but I want to reflect the data in each sheet. So
>>> each sheet has its own graph, which looks similar but reflects data on
>>> the sheet.
>>> Problem I am having is when I copy/paste it it paste absolute reference
>>> , so I have manually to fix data range and with that many sheets its
>>> kinda cumbersome.. and this is not the only file
>>> Any suggestions how I can copy my chart so it reflect the data on the
>>> sheet that is copied to?
>>>
>>> Thanks,
>>> Sam
>> .
>>

Thinking outside the box:
if your data is basically the same layout, same number of series and
categories etc, then select your chart, go to "save as template" and
give it a sensible name.
Now on each sheet you need to select your data, insert a chart and
choose your template as the chart type.

You could even macro record the insert job, andy postioning and resizing
etc, then repeat this on every sheet.

Hope this helps (even though it does not actually copy your chart at all!)

Adam

On 04/03/2010 21:15, Sam wrote:
> Hi,
>
> I have workbook with about 50 sheets. they have exactly the same table ,
> just data(numbers in the table) are different. Also created a chart
> based on the table in the first worksheet. Chart is embedded in the
> sheet). I spend some time to format the chart and want to copy and paste
> it on the other sheets, but I want to reflect the data in each sheet. So
> each sheet has its own graph, which looks similar but reflects data on
> the sheet.
> Problem I am having is when I copy/paste it it paste absolute reference
> , so I have manually to fix data range and with that many sheets its
> kinda cumbersome.. and this is not the only file
> Any suggestions how I can copy my chart so it reflect the data on the
> sheet that is copied to?
>
> Thanks,
> Sam