I think if I had to do this, the first thing I would do is put all the data from
the dozen worksheets/workbooks into one worksheet.
Then just use =vlookup() against that single worksheet.
Ron de Bruin has some sample code that may help:
> Apologies if this has been asked before, but I'm a bit of a newbie when
> it comes to mucking around in excel, I've had a search but I don't
> really know what to call what I want to do, so finding the answer is
> Anyway. I have a dozen or so workbooks that are all of the same
> format, 1 work sheet with a basic list of test scenarios in each, with
> a unique reference for each row.
> What I'd like to do is pull information from all those workbooks into a
> separate workbook by the use of the unique reference. i.e If I type
> in the unique reference in the master workbook, it will search through
> the dozen workbooks and pull the information relevant to the key via a
> I know how to do a vlookup across workbooks on a one-to-one
> relationship, but recursing that through a dozen files in different
> directories is stumping me.
> Is this possible? And if so, how would I do it?
> Any pointers or examples would be much appreciated.
> email@example.com (remove the obvious)
> GamerTag: TrooperNeil