consolidate data from many workbooks in same folder

[sparx007]

I have a bunch of market reports

I have a number of identically formatted excel market reports (one per country), with different names, in one folder

I need to extract data to a consolidation workbook.

Eg. competitors, on a worksheet named "3.Competition" in all wbooks.
Here would be a list of competitors in this country and their expected sales.

I want to consolidate this like competitors in one column and then all our countries in a row and then populate below with data from the individual reports.

How am I to do that with formulas, because I could of course just have someone copy all this data manually. Hard not smart - but is it possible?

Pls. help...

[keithfender]

maybe not what you are looking for, but you could always just make ONE Excel file from all the data, and add an extra column for Country. Then you wouldn't have to pull from several different sheets.

Or you could use VLOOKUPS to reference the other worksheets, but this way sounds like more trouble/work. Having another column to signify country makes more sense to me, and just have a "Master" data page that you create all your reports from.