multiple Charting issues

Hi,

I have a spreadsheet with numbers colerating to dates and subjects for pupil
marks.
The workbook has several sheets, one for each pupil.

My Problem:
I need to create charts in a seperate workbook (one for each pupil), with
worksheets then breaking down the subjects. each subject has a chart for
winter, spring, summer and total date ranges.

This works out as 4 charts per subject, 8 subjects per pupil. 50 pupils =
1600 charts i think.

Is there any way i can automate the chart creation, and ensure the data
sources don't keep changing to: location of the book and then the
spreadsheet and cell range from the only way it works of the speadsheet name
and cell range. This keeps hapenening to me whenever i move the
foldercontaining everything. which i need to do.

Many Thanks in advance; no doubt you will hear from me again

alan

Alan -

I would suggest this approach. Make a single workbook for a single
student, with the data sheet arranged appropriately and the charts
formatted as needed. Make a copy of this workbook for the next student,
copy the next student's data, and use Paste Special from the edit menu
to replace the first student's data with the second student's. It isn't
truly automatic, but the manual content is pretty straightforward.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______

Swalcliffe Park wrote:
Hi,

I have a spreadsheet with numbers colerating to dates and subjects for pupil
marks.
The workbook has several sheets, one for each pupil.

My Problem:
I need to create charts in a seperate workbook (one for each pupil), with
worksheets then breaking down the subjects. each subject has a chart for
winter, spring, summer and total date ranges.

This works out as 4 charts per subject, 8 subjects per pupil. 50 pupils =
1600 charts i think.

Is there any way i can automate the chart creation, and ensure the data
sources don't keep changing to: location of the book and then the
spreadsheet and cell range from the only way it works of the speadsheet name
and cell range. This keeps hapenening to me whenever i move the
foldercontaining everything. which i need to do.

Many Thanks in advance; no doubt you will hear from me again

alan

What about the series information on the charts, whenever i reopen the chart
and update the charts, with or without the linking workbook open, the data
source has a file stucture reference like ='[c:\my worksheets\workbook.xls']
rather than just the workbook name.

Also, this system needs to be created year on year with the updates of dates
and pupils. what would be the best option for ensuring next year, someone
who knows hardly anything about excel other than how to enter in the numbers
and look at the charts could maybe manage this.

Many thanks

Alan

well done on the mvp, i looked at your website before posting, very
resourceful.

"Jon Peltier" wrote in message
...
Alan -

I would suggest this approach. Make a single workbook for a single
student, with the data sheet arranged appropriately and the charts
formatted as needed. Make a copy of this workbook for the next student,
copy the next student's data, and use Paste Special from the edit menu
to replace the first student's data with the second student's. It isn't
truly automatic, but the manual content is pretty straightforward.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______

Swalcliffe Park wrote:
Hi,

I have a spreadsheet with numbers colerating to dates and subjects for
pupil
marks.
The workbook has several sheets, one for each pupil.

My Problem:
I need to create charts in a seperate workbook (one for each pupil),
with
worksheets then breaking down the subjects. each subject has a chart for
winter, spring, summer and total date ranges.

This works out as 4 charts per subject, 8 subjects per pupil. 50 pupils
=
1600 charts i think.

Is there any way i can automate the chart creation, and ensure the data
sources don't keep changing to: location of the book and then the
spreadsheet and cell range from the only way it works of the speadsheet
name
and cell range. This keeps hapenening to me whenever i move the
foldercontaining everything. which i need to do.

Many Thanks in advance; no doubt you will hear from me again

alan