MrExcel Publishing
Your One Stop for Excel Tips & Solutions


Posted by Don on January 10, 2002 2:01 PM

I've made 52 workbooks, each containing an expense report spreadsheet. Each is named with week ending dates ex. jan6-2001 and jan13-2001. Each spreadsheet is identical. I need to combine all of the workbooks into one year end summary. Is there any way to do this using a simple formula seeing as they are all different workbooks and not simply different worksheets contained in one workbook? Any help for this old dumb sales guy would be greatly appreciated!

Posted by Richard S on January 10, 2002 2:59 PM

If they are all EXACTLY the same, you could use the Data menu, consolidate option. In the past, I have used this to concolidate budgets. In the reference part, browse to get your file name, then enter the cell range, and press add, then continue on for each file you want to consolidate. It will accept wild cards, so if all the files have a similar name convention, you could just enter one reference that picks them all up, ie *2001.xls!$B$2:$Z$53. I think the consolidating file needs to be in the same directory.

Posted by Don on January 10, 2002 5:22 PM

Many, many thanks, especially for the wildcard tip, it worked fantastically once I figured it out and saved me days of manual typing!