embedding +concatinate string in lookup

HEllo there,

Hope someone has a solution to my problem.
I've just started with excel2000 and was hoping to learn it by practice in stead of books.
For my work i am trying to extract some data from an output sheet that displays a group of people with per individual 14 stats concerning timings and quantaties. These sheets come out every week and have always the same layout except for changes in the number of people that are on it. Straight forward: individuals in the first column and stats per person on the same row.

I need to get all the week stats of each person and put them on one sheet and eventually one chart, dynamically.
So my idea was to have all the .xls' in one folder then.
use the command "lookup" with "value" being name, "vector" being the first column of the sheet from the file AAweek39.xls and then the "result vector" being $B$1:$B$20, so for instance the value of the cell[Hans/B].

Please Login or Register  to view this content.

Works like a charm and i can change the name and get all that persons stats.
I dont know yet how i can generate it for all the cells or rows, but i was planning on doing this step by step and i wasnt there yet.

But to make it really dynamic it has to get the stats from all weeks from file AAweek39.xls to AAweek52.xls.
So checked out the library and came up with this:

Please Login or Register  to view this content.

Simpel version and without path but its more clearer as a function now. It has to take weeknumbers from one colummn and then get the data and put it all on the row of that week.
The weeknumbers i would generate in the first column with some sort of built in function.

So now i only had to embedd it into the last function and i would be there. Nope , I can't get it to work. this is what I made:

Please Login or Register  to view this content.

I tried different things like putting different kinds of brackets around the concatenate function and other stuff but it just wont work. I think the problem is that excell sees this as just a text but i cant find a function that would change it into a legit value. I've been googling and searching the helpfiles for a few hours now so pls help. Thank you for your attention



PS.
I dont know if it matters but this is not a comercial project of mine. I do this entirely for free and just as a sort of hobby or project to get to know excell(doing this for my teammanager actuall .