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

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

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

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

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

Hi

So your source files are on some network resource?

I myself never use direct addressing in such cases. In local computer, I map
the network resource, and then refer to mapped resource. I.e. when your
source data are in \\server\Statements\ share, then in local computer I map
this share (p.e. as X), and the link will be like:
='X:\[Agnes 1027.xls]Account 4'!$B13

The advantage is, that when source files are moved to another share, or the
share is renamed, then I recreate the mapping, and all links work as
before - no need to edit the formulas. And of-course link formulas take less
room.

As you didn't get working formulas, somewhere something went wrong. I
explain it again - stepwise (to be sure, I checked it all, and it did work)

In my computer, I have a folder C:\Documents and Settings\MyProfile\My
Documents\, and in this folder an Excel workbook Liikumised.xls. There is a
sheet Liikumised in this workbook. I generate links to some range on this
sheet into a new workbook.

Liikumised.xls is closed - I only need to know the sheet name, and the table
placement.

I create a new Excel file. On Sheet1 in it, into cell I1 I enter the string
"C:\Documents and Settings\MyProfile\My Documents\"
Into cell J1
"Liikumised.xls"
Into cell K1
"Liikumised"

Into cell A1 I enter the formula:
="_='" & $I$1 & "[" & $J$1 & "]" & $K$1 & "'!" & ADDRESS(ROW();COLUMN();4)
and copy the cell to range A1:D30

Now I copy the range A1:D30, and use PastSpecial with Values checked to
replace all formulas with their values. P.e. in A1 I get the string
"_='C:\Documents and Settings\MyProfile\My
Documents\[Liikumised.xls]Liikumised'!A1"

As in new document all cells are formatted as General, I don't need to check
the range formatting. So with range A1:D3 selected, I select from Edit menu
Replace, enter '_' into 'Find what:' field, and click on Replace All
button - and all formula strings in whole range are replaced with links to
Liikumised.xls


Try this out with some existing workbook on your computer. After that try it
with source file on mapped network resource, etc.

I suspect, that you have something wrong with link syntax. To get right link
syntax, open some source file, in destination workbook into any free cell
enter the equal sign (=), activate the source workbook and point to some
cell in it, and press enter. After that close the source file (the link
formula to closed file is different compared to opened one, and is adjusted
automatically whenever you open or close the source workbook) - in
destination cell you can now examine the proper link.

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



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