copying a chart in Excel 1

[angler2229]

At our school we are charting our attendance for several weeks, so I am building a spreadsheet for every teacher to use.

I started a workbook that contains the attendance data and made a customized chart. Then I copied the data to all the other sheets. I also copied the chart to the other sheets, but of course, when I change the data in the first sheet, the charts on the other sheets change too.

Here is the question. Can I break the links to the other charts and make a new link between the data on that sheet?

Thanks,

Daniel

 

[shetlandbob]

change the source of the chart.

Right click - goto source data and manually change the sheet name that the source data refers to. If the format of the sheets is the same then this should work.

Its tedios - but.....

Or

You could be clever and record a macro that does it and set the sheet name as a variable that you could change - but if you dont know/care about vba this would probably take longer than actaully changing them manually.



Robert Cumming

http://www.consultantengineer.co.uk

 

[xlhelp]

A shorter step might be to go to Edit, Links; change Source and select the current workbook name for each of the workbooks

It's your choice which one to use

http://www.geniusmedia.ca

 

[shetlandbob]

edit links would be if the source data is in a different workbook.

I was under the impression it was the same workbook - different sheets?

Robert Cumming

http://www.consultantengineer.co.uk

 

[xlhelp]

I should learn to read!

http://www.geniusmedia.ca

 

[SkipVought]

Hi,

Why are you creating a ZILLION charts when ONE will do?

Here are the guidelines that a professional would use...

1. a SINGLE data table.

2. a SINGLE chart linked to the SINGLE data table.

3. With the chart selected -- Tools/Options - Chart Tab -- Plot visible cells only

4. on the SOURCE DATA sheet, Data/Filter/Autofilter -- turn on the macro recorder and select a student -- turn off the recorder

5. Create a UNIQUE list of Teachers.

4. Insert a Forms Drop Down on the chart -- reference the UNIQUE list of Teachers in the Input Range -- reference a cell on another sheet for the Cell Link, lets assume Sheet2!A1. In the adjacent cell Sheet2!B1=INDEX(reference the UNIQUE list of Teachers,A1,1)

5. In your recorded macro, where the TEACHERS NAME is referenced, substitute =Sheet2!B1

Now, whenever a new Teacher is selected from the drop Down, their data attendance is plotted on the chart.

Cuts WAY DOWN on maintenance!



Skip,
[sub]
[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine![/sub]

 

[angler2229]

Robert,

That worked just fine!

Thanks much!

Daniel

 

[angler2229]

Skip,

I'll have to try your method. The people who dictate what kind of data we keep are a unique and somewhat strange breed. They like all those files!

Ever heard of Baldridge? Data, data, data!

Daniel

 

[SkipVought]

That's what happens when USERS dictate on technical issues. It almost ALWAYS ends up with WAY more wasted effort.

Skip,
[sub]
[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine![/sub]