In this case a macro is your answer.

If you have a list of your 100+ workbooks in a spreadsheet or they sit
in a small number of folders you can loop through them opening one by
one looking for the data relevant to each summary sheet.

Try recording one and see how you get on. Using the dir() function will
enable you to extract filenames from folders - see the Help files for
details.

You might also consider getting your data into a database which would
make this sort of summarisation so much easier.


--
mrice

Research Scientist with many years of spreadsheet development experience
------------------------------------------------------------------------
mrice's Profile: http://www.excelforum.com/member.php...o&userid=10931
View this thread: http://www.excelforum.com/showthread...hreadid=548328