Example:

worksheet1.xls
A B
1 Albert Operations
2 Toronto

worksheet2.xls
A B
1 Peter Marketting
2 Chicago


worksheet3.xls
A B
1 Mark Sales
2 New York

I need to collect summary from many worksheets and create a list in a new
worksheet and I got the list of all filenames and put them in one colum. I
am trying to write a formula so that I dont have to edit every formula in
the summary worksheet to enter the filename. How can I make the formula that
references to the external worksheet to get the name of the worksheet from a
cell?

Here's what I am expecting to have in the new summary worksheet:

A B
C ....................... Z
1 =[z1]Sheet1'$A1 =[z1]Sheet1'$A2 =[z1]Sheet1'$B1
worksheet1.xls
2 =[z2]Sheet1'$A1 =[z2]Sheet1'$A2 =[z2]Sheet1'$B1
worksheet2.xls

instead of
A
B C
1 =[worksheet1.xls]Sheet1'$A1 =[worksheet1.xls]Sheet1'$A2
=[worksheet1.xls]Sheet1'$B1
2 =[worksheet2.xls]Sheet1'$A1 =[worksheet2.xls]Sheet1'$A2
=[worksheet2.xls]Sheet1'$B1

in such a way that after I have entered the formulas with referces in one
row, that I can copy them down to the rest 100 rows and each row would refer
to the filename in column Z

I hope someone can get me the answer soon so it would save me a lot of time.
Thank very mcuh in advance. I would really apprecaite your input.

Art