Excel 2007 : Match function in different databook

[brianp]

Hi everybody,

It's my first post, I apologize in advance for any error in presenting the thread.

Here's the thing: i have about 150 excel files, made by the combination of 10 months and 15 types of plants.
So the xls names are something like "01-plant1, 02-plant2 .... 10-plant15"

The structure of the files is almost the same: i have on column A the information I need to pull:
- capacity of the plant
- production of the plant
- consumptions of the plant

It will show something like this

Capacity
- Product A
- Product B
- Product C

Production
- Product A
(....)

What i need to pull is the name of each product produced for each plant in each month.
The formula I have written is the following:

(OFFSET(INDIRECT("'["&D$1&$B3&".xls]Rese Tecniche'!"&ADDRESS(MATCH($A3;'[01-alky.xls]Rese Tecniche'!$A:$A;0);1);1);$C3;0));"")

(an index match would have worked as well)

As you can see i'm using "&" signs in order to parametrize in the indirect the file source.

Here is the issue: i'm not able to do the same thing in the match function. (the part in bold)
(OFFSET(INDIRECT("'["&D$1&$B3&".xls]ReseTecniche'!"&ADDRESS(MATCH($A3;'[01-alky.xls]ReseTecniche'!$A:$A;0);1);1);$C3;0));"")

so 01 should be flexible to change in 02, 03, 04 as it is in the first part of the function and alky should do the same.

When I try to switch i get #VALUE error and analizing outputs by F9 that part of the formula i see that i get the correct string but with " before and after. If i remove manually the " before and after the formula works.

Can anybody help me?

Thanks a lot
Brian

[brianp]

by the way, I obviously have to keep all the 150 files open while pulling the data, i'm aware of that and it's ok!

Thanks in advance for your help!

[brianp]

sorry for UPping, I have to make it work in a few hours..