Hi

How are data grouped into all those 200 files - is there some 4th parameter,
or are those files for monthly data, or region data?

Let's assume you have some 4th parameter (Parameter) by which data are
grouped to files (when not, then the solution will be simplified). And that
in all files data are on sheet Data.

Create a sheet Links with headers in row 1: Parameter, Region, Month,
Amount.
Create a sheet Files with table Parameter, File. Enter into this table all
filenames, when all files are in same folder, or filenames (enclosed into
square brackets) preceeded with full path, when they aren't in single
folder. For every filename, enter Parameter. You get a table with data in
range A2:B201

On sheet Links, into cell A2 enter the first parameter from sheet Files, and
copy it down to A301. Into A302 enter the 2nd parameter from sheet Files,
and copy it down to A601, etc. until all 200 parameters have 300 rows in
table Links. When you didn't enter full paths along with file names (all
files were in single folder), enter this path into some cell on sheet Links,
p.e. E1.

Into row2 enter formulas like (this one is for case the path is entered into
cell E1)
B2: ="='" & $E$1 & "[" & A2 & "]Data'!A" & ROW(B2)
C2: ="='" & $E$1 & "[" & A2 & "]Data'!B" & ROW(B2)
D2: ="='" & $E41 & "[" & A2 & "]Data'!C" & ROW(B2)
Copy those formulas to range B2:D302.
In row 302, edit formulas to be like
B302: ="='" & $E$1 & "[" & A302 & "]Data'!A" & ROW(B2)
C302: ="='" & $E$1 & "[" & A302 & "]Data'!B" & ROW(B2)
D302: ="='" & $E$1 & "[" & A302 & "]Data'!C" & ROW(B2)
, and copy those formulas to range B302:D602
etc. for all 300-row groups.

Select the whole range with formulas on sheet Links (B2:D30002 ?), and copy
them. Then PasteSpecial Values, and after that replace all "=" with "=".
When all was done properly, you get working links to all 200 files (The
formulas above were typed on fly, you have to check them. And maybe you have
to include the check for empty cell into link formula too {so the result
formula will be something like =IF(Link="","",Link)}.

Whe you get all links working, define a fixed named range, p.e. SourceData,
as
=Links!$A$1:$D$30002
(adjust the range to your real table).

Save the workbook. Now on another sheet, select A1, and create an ODBC query
from Excel table. Give your workbook as datasource, and the named range
(SourceData) as table. Set the condition for column Region to Not Null, and
finish. You get the table without gaps.



--
Arvi Laanemets
( My real mail address: arvil<attarkon.ee )


"hkappleorange" wrote in message
...
I have a simple task on Excel but I cannot figure it out. Pls help:-

I have 200 separate Excel files with data all in 3 columns: Region, Month
&
Amount. They each has a range of records from 100 to 300 lines each.

I am asked to create a single 3 column table with these 3 fields
containing
all data from these 200 tables in separate files.

How many ways we can do this ? Please help.....