Thank you for the reply. I think the techinque should work, I still must be
doing somthing wrong.
I did exactly all that. just the resulting string is a the string and does
not convert into a formula. When I manually edit it and place the = sign at
the begining, it opens up the browse folders .. if i point to the
corresponding file it converts to the formual and (this part I didn't
understand why it repeacts the sheet name) the result is similar to:
='\\server\Statements\[sheets only[Agnes 1027.xls]Account 4]Account 4'!$B13




"Arvi Laanemets" wrote in message
...
Hi

You did left out the final step - replacement of "_" in all formula strings
with nothing!

Select the range with gotten formula strings;
Check that the range is formatted as General;
Select Replace from Edit menu - into 'Find what:' field enter '_', leave
'Replace with:' field empty, and click on 'Replace All' button.


Arvi Laanemets


"Falcon Art" wrote in message
. ..
Thank you Arvi. That does do the combination I wanted but the result in
the
cell is the formula itself as text and its not executed. The result I get
is
_'\\server\path[Worksheet1.xls]Sheet 1'$A1 instead of the value

I appreciate your quick response. I wish you can respond pretty quick to
tell me where I went wrong.

Thanks

Art

"Arvi Laanemets" wrote in message
...
Hi

A way to do it at design time:

At start, add path for workbooks too - so that you don't need to open all
external workbooks at design time. P.e. into column Y, like:
Y1="C:\Documents and settings\YourProfile\My Documents\ProjectData\"
Z1="worksheet1.xls"

Into cell meaned to refer to Sheet1!A1 in workbook Worksheet1.xls, enter
the
formula
="_'" & $Y1 & "[" & $Z1 & "]Sheet1'$A1"
and copy the formula down to wanted range. Format the range with copied
formulas as General, copy the range and paste into same location as
Values,
and then, leaving the range selected, do Replace All "_" with nothing.
When
all was done properly, then you get functional formulas instead of text
strings.

--
Arvi Laanemets
( My real mail address: arvil<attarkon.ee )



"FalconArt" wrote in message
.. .
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