indirect function help!

[bluepenink]

Hi all

im trying to compile some data together for a contest for sales employee revenue.

each week i get from 33 branches, all 200 sales ppl data (the worksheet are named after their branch i.e. Washington-Philadelphia); is there are seperate worksheets for individual worksheets, with D1 holding their names.

file path: i.e.
='J:\Sales\SWT\2011\P3W1\[Washington-Philadelphia.xls]John Doe!$Q$21

master workbook, I8 = P3W1
master worbook, E9 = John Doe
master workbook, D9 = Washington-Philadelphia

now can i possible get a indirect function to scan for the name and then do find data for me in certain columns/rows?

so basically, just need some help or if there is another method for a formula to scan thru the workbook, use the appropriate name and then get the data i want.

the data will be dumped into my "master sheet"

thx you so much for the input!

 

[bluepenink]

i have tried to achieve something, but not getting the right placement of maybe "" or something....getting a value error:

=INDEX("'J:\Sales Analyst\SWAT\Archieve 2011\"&I8&"\["&D9&".xls]"&E9&"'!"&E4&"",I4,I5,1)

this is what it should translate to say:
=INDEX('J:\Sales Analyst\SWAT\Archieve 2011\P1W1\[Edmonton-Winnipeg.xls]Peter Vepstas'!$A$1:$X$63,I4,I5,1)

I8 = P3W1
D9 = Edmonton-Winnipeg
E9 = Peter Vepstas
E4 = A1:X63 (the range)
I4 = 21 (row #)
I5 = 17 column #

thxs