Extracting student data from multiple files

[johnl - ExcelForums.com]

Hey
I really hope someone here can help me
I am at present working in a university and have been given the tas
of automating the task of processing student grade requests
Allow me to explain

We have the grades stored beside the name and number of the student i
an Excel file, one for each year (JF, SF, JS, etc.) and for eac
calender year (2002, 2003, etc.) for each class

So we have JSPhysics2003.xls as an example

Students come in and want to see their progress over their whole time
which necessitates someone trawling through all the files to pick ou
the entries relating to that student

What I want to do is to be able to enter a student's name or cod
number and have their data for each year and class etc. appea
below

What ways can I do this

I've tried things like Lookup, Filter and PivotTable but I eithe
can't figure them out or they don't seem to play too well wit
multiple files or sheets

Please someone help

Thanks
Joh

 

[Frank Kabel]

Hi
this sounds more like a database application but an Excel task. So you
really may consider migrating this to for example MS Access

 

[Dave Peterson]

I think I'd take the time to put all the data in one worksheet in one
workbook--if you have less than 64k rows worth of data????

Then make sure there's information on each row that identifies person, class,
year, etc.

Then use Data|filter|autofilter and just show the rows that match my criteria.

I don't think I'd build this workbook more than once per grading session--just
keep it until the new grades come out and then toss and rebuild.

Ron de Bruin has some examples on how to merge data from various
workbooks/worksheets at:

http://www.rondebruin.nl/copy3.htm