Thank you Arvi for the very detailed explanation. I did it exactly as you
have described.
1. The ADDRESS(ROW();COLUMN();4) part did not work for me, and besides I
needed B13 as the address so I replaced it with that in quotes.
2. After doing the replace _ I would be prompted to select the file with the
browse window. and it prompted me for each of the 132 files.
3. I repeated again for a couple of rows and did it with no path since I
want to place the new worksheet in the same folder as the rest of the
worksheets. Again it prompted me to select the file after I did the replace
all for _
4. Now that I have selected the file related to that row, it displayed the
right value from the source file. I couldn't find any difference in the
formula. But I wanted to remove a $ sign from one of the reference and when
I edited the formula at this stage, it again prompted me to select the file.
Does this give you a clue of why or what is wrong with the file referecne,
even after once I selected the file and when in to change the formula? Would
there be something in the Excel preferences? This is Excel 2003 btw.
Once again, thank you very much for the detailed reply. I very much
appreciate it. If this works out fine, it would save me time everytime I
have to create a summary from all the files.
Thanks,
Art
"Arvi Laanemets" wrote in message
...
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
|