VLOOKUP across several external files

[cgrady]

Hi there,

This is my first post here. Any help would be appreciated.

Here is my problem: I have a master spreadsheet that contains a list of 250 student names.

That file has been split into eight smaller spreadsheets so that teachers can enter details about the students.

I would like to use a vlookup formula to update the entries on the master spreadsheet as the smaller ones are filled in.

If I tell the vlookup formula which spreadsheet to look at I can make it work. But is there a single formula I can use that will search through one file, then, if it doesn't find the name, search through the next file and so on.

In other words I need a vlookup formula that can search through eight different external spreadsheets looking for a single entry.

Here is an example of the Vlookup formula that works with just one spreadsheet:
=VLOOKUP(D:D,'[Y7 Annual Report_English_7A.xls]Subject Report'!$C:$N,8,FALSE)

I would like it to go on to search through "Y7 Annual Report_English_7B.xls" and so on.

I've been looking through google and the forums but can't seem to make anything work.

I tried the following IF(ISNA formula but it only returns an NA value when I know the information it should be returning is in the referenced spreadsheet:

=IF(ISNA(VLOOKUP(D:D,'[Y7 Annual Report_English_7A1.xls]Subject Report'!$C:$N,8,FALSE)),"",VLOOKUP(D:D,'[Y7 Annual Report_English_7B.xls]Subject Report'!$C:$N,8,FALSE))

[VBA Noob]

cgrady,

Welcome to the forum. Please read the forum rules below and then add the link to the other forum where this question has being posted

VBA Noob

[cgrady]

I'm sorry if I've accidentally broken a forum rule.

Here is the link to the other forum where I've already posted this question:
http://www.mrexcel.com/forum/showthread.php?t=320224

I didn't get a response I could make work so I thought I'd try somewhere else.