Excel 2007 : Capturing Data from the same cells in multiple tables

You're consolidating the ranges in what way - listing data tables one below the other or aggregating the data into one master table in some way (ie numerics - addition, division etc...) ?

I'd suggest posting a much simplified sample... you need not demo a file with 200 sheets (!) nor even fully populate whichever sheets you do opt to include - all we really need is:

-- a file with say 3 sheets
-- each of which has data in the range of interest (only)
-- the sheet names and data within should reflect your file (in terms of data types etc...ie numbers, strings etc)
-- outline via a summary sheet your "ideal" results.

It would also be worth stipulating whether or not you're open to VBA - if you are looking to list the 200 ranges separately a VBA routine would be sensible... for aggregation of numbers you can use 3D calculations, eg:

=SUM(Sheet1:Sheet200!A1)

It's 6pm on a Friday for me so I might not be around myself to help you this evening however if you post the above you're sure to get a good response.

I wanted to update this post. Here is what I would like to do. As stated, I have a spreadsheet with 200 worksheets within the spreadsheet. On line 25 of each worksheet there is data in 5 cells that I want to capture and pull into one spreadsheet. I would like each sucessive line of data to be placed in the following line. So the 5 data points from the first worksheet would appear in line 1, the 5 data points from the next worksheet would appear on line 2, etc.

Here is an example of the output. The ABC represents the colume headings and the 123 represent the rows.

----A--B--C--D--E
1 10 12 15 16 11
2 12 14 16 12 14
3 11 13 17 16 15
4 15 14 18 14 16
5 16 17 16 12 14