wilmott.com

Hi allWondering if anyone could please shed some light on this!We are underwriting hundreds of loans and have decided 1 worksheet per loan in 1 global workbook is too cumbersome (+ 15MBs). We therefore want to have a hundred separate files in 1 folder, each with proper file naming syntax, and have a Summary roll-up workbook that upon the ‘push of a button’ goes in and grabs cells or a range from all of the individual files and aggregates them in the Summary workbook (ie a master datatape). Would prefer to use Concatenate and Indirect functions but the 100 workbooks would need to be open for this function to work? Any other ideas?Thx!!

Buy a database (Access is not a very good database, but it's light years ahead of Excel for what you want to do).Failing that, store the data in ASCII files and have a macro that generates a spreadsheet from either one or all files in the directory.As a last resort, learn about DLLs. These allow you to do what you want without opening the files, but if you're a good enough programmer to use these, you could instead pick a language better suited for your task.

Thanks very much for you help Aaron!Best Regards,Grant

VBA Macro should do your work I guess

you could some time on defining the attributes of loan(anyways you would have to do this design part if you opt for databse) after this i guess VBA should be enuff if loans are mostly of the same type..if there are 3 major type of loan categories then look at having 3 databses... my conclusion VBA would suffice.