Automatic, conditional chart creation from separate files [Excel2007]

[Piotr]

I have a number of separate files (Excel 2007). Each file contains
several sheets with test results of a few students. The students (the
same, all the time) are evaluated each month using the same test, and
each month a new sheet is created for each of them. For example:

Workbook 1: Worksheet A (John), Worksheet B (Jennifer), Worksheet C (Anna)

Workbook 2 (a month later): Worksheet A (Jennifer), Worksheet B (John),
Worksheet C (Anna)

I`d like to create a separate file with charts depicting the mean
results for each student. The chart would retrieve data automatically
from every file stored in a particular folder (including files to be
created later), but in the following way:

The chart for John:

Retrive data from every file in a given folder, from a given range, but
only if the worksheet contains "John" in L2.

In this case, Excel would create a separate file with three charts,
retrieving data from Workbook 1 AND 2, but from worksheet A OR B OR C.


Is that possible? Could anybody help with a VB Script or Macro? I don`t
think it`s possible without programming, which I can`t at all, is it?

Thanks in advance
Peter, Poland