Searching cells inside multiple spreadsheets inside subfolders returning vlookup 2010

Hi everyone!

As it's my first post a brief intro + this leads on to what i'm trying to achieve.

I'm an IT Teacher at a secondary school - I have some knowledge of Java and C# but next to no knowledge of VB =/

I am using Excel 2010.

My fellow teachers would like a way to bring together tracking information on students so heads of department and heads of year can easily see a summary of which students are struggling.

This tracking information is stored in one particular folder - then inside this is a folder for each department. Inside this are subfolders for each teacher in that department and inside there is a spreadsheet for each class that teacher teaches.

Example:
My teacher initials are DB..my class spreadsheets would be in:
O:\Tracking info\ICT\DB

A geography teacher's would be in
O:\Tracking info\Geography\CS

Each teacher has on average 10 classes.

Example:
Inside my folder I would have a spreadsheet called 7OIT 8DIT 8RIT 103IT etc.

Inside each class spreadsheet is the name of the student and a letter to indicate how they are getting on followed by a comment.

My goal is to find all students that have a R indicating they are having trouble (Red Amber Green).

e.g.
A5 B5 C5
Tom Smith R "Keeps throwing pencils around"
A6 B6
Jenny Hill G

Ideally inside each department folder I'd like to make a spreadsheet with a summary of all students who are a concern. It would search through all spreadsheets that are in subfolders for that department (hence all the classes of all staff in that department) and return the name of any students who had an R next to their name and also the comment that was entered.

Any and all advice you can give is gratefully received! =)

Darren Beasley

Hmm - I might force each teacher to use just one class sheet with multiple classes all on one worksheet.

I can then simple hardcode vlookups to search the teachers in that department.

The challenge then becomes creating a summary sheet for a head of key stage 3 who wants to know which students in Year 7 have more than 2 'R's registered for them in any class.. =/