Thank you sooo much for your paitence with me. I need to be more alert and
read carefully. It is done exactly how you have described it to be done.
What I was doing was 'exactly' what you said to do. But I overlooked that
the file names were like 'Agnus_1027.xls' and thus while doing the replace
all step, I was replacing the _ in the filename as well. When I noticed that
and did replace '_=" with '=' it worked like a charm.
Once again. I am very thankful to you for your quick replies. Only I should
have been more paitent and shouldn't have paniced with the stress of time I
had.
Thank you.
----- Original Message -----
From: "Arvi Laanemets" <
[email protected]>
To: "Falcon Art" <
[email protected]>
Sent: Monday, August 22, 2005 1:32 AM
Subject: RE: how to reference external refereces from a list
Hi
About ADDRESS function - I missed to replace semicolons (separator
character in my Regional Settings) with commas in function.
I tried to get the same behaviour (Open file dialog) for test link - the
only way to achieve this was to have some typing error like
additional/missing space or wrong character in path or file name part of
it. Any error in sheet name does activate 'Select Sheet' window, an
error in cell reference returns 'The formula you typed contains an
error' message.
I myself use Access2000, but as much as I know, links must work in same
way in both versions. It looks like whenever you edit/create some link,
some part of path or file name is modified automatically. I don't have
any reasonable explanation for such behaviour - only that maybe you
check, don't you have some code in worksheet's Change event (how about
checking some link in newly created workbook - and do this with any
other workbooks closed), or don't you have some 3rd-party Add-In
activated (which projects are visible in VBA Project window?). And
probably check your computer for spyware or/and viruses - sometimes they
are the cause for strange behaviour of applications.
Arvi Laanemets
-----Original Message-----
From: Falcon Art [mailto:
[email protected]]
Sent: Monday, August 22, 2005 1:42 AM
To: Arvi Laanemets
Subject: Re: how to reference external refereces from a list
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
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
30
Now I copy the range A1
30, 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
3 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<at>tarkon.ee )
Falcon Art said:
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
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 said:
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
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<at>tarkon.ee )
FalconArt said:
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
