Thread: how to reference external refereces from a list

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" <garbage@hot.ee> wrote in message
news:O$Obb78oFHA.2080@TK2MSFTNGP14.phx.gbl...
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" <falconart6@sympatico.ca> wrote in message
news:2QVMe.12295$7R.676080@news20.bellglobal.com...
> 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" <falconart6@sympatico.ca> wrote in message
news:D81Ne.16988$kz6.987865@news20.bellglobal.com...
> 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" <garbage@hot.ee> wrote in message
> news:O$Obb78oFHA.2080@TK2MSFTNGP14.phx.gbl...
> 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" <falconart6@sympatico.ca> wrote in message
> news:2QVMe.12295$7R.676080@news20.bellglobal.com...
> > 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" <arvilaanemets@hot.ee> wrote in message
news:uXps%23WCpFHA.3364@tk2msftngp13.phx.gbl...
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" <falconart6@sympatico.ca> wrote in message
news:D81Ne.16988$kz6.987865@news20.bellglobal.com...
> 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" <garbage@hot.ee> wrote in message
> news:O$Obb78oFHA.2080@TK2MSFTNGP14.phx.gbl...
> 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" <falconart6@sympatico.ca> wrote in message
> news:2QVMe.12295$7R.676080@news20.bellglobal.com...
> > 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<at>tarkon.ee )



"Falcon Art" <falconart6@sympatico.ca> wrote in message
news:EW6Ne.20416$kz6.1048597@news20.bellglobal.com...
> 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" <arvilaanemets@hot.ee> wrote in message
> news:uXps%23WCpFHA.3364@tk2msftngp13.phx.gbl...
> 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" <falconart6@sympatico.ca> wrote in message
> news:D81Ne.16988$kz6.987865@news20.bellglobal.com...
>> 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" <garbage@hot.ee> wrote in message
>> news:O$Obb78oFHA.2080@TK2MSFTNGP14.phx.gbl...
>> 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" <falconart6@sympatico.ca> wrote in message
>> news:2QVMe.12295$7R.676080@news20.bellglobal.com...
>> > 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 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" <garbage@hot.ee> wrote in message
news:eXprOKIpFHA.3760@TK2MSFTNGP10.phx.gbl...
> 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<at>tarkon.ee )
>
>
>
> "Falcon Art" <falconart6@sympatico.ca> wrote in message
> news:EW6Ne.20416$kz6.1048597@news20.bellglobal.com...
>> 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" <arvilaanemets@hot.ee> wrote in message
>> news:uXps%23WCpFHA.3364@tk2msftngp13.phx.gbl...
>> 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" <falconart6@sympatico.ca> wrote in message
>> news:D81Ne.16988$kz6.987865@news20.bellglobal.com...
>>> 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" <garbage@hot.ee> wrote in message
>>> news:O$Obb78oFHA.2080@TK2MSFTNGP14.phx.gbl...
>>> 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" <falconart6@sympatico.ca> wrote in message
>>> news:2QVMe.12295$7R.676080@news20.bellglobal.com...
>>> > 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 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" <Arvi.Laanemets@tarkon.ee>
To: "Falcon Art" <falconart6@sympatico.ca>
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:falconart6@sympatico.ca]
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

"Arvi Laanemets" <garbage@hot.ee> wrote in message
news:eXprOKIpFHA.3760@TK2MSFTNGP10.phx.gbl...
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<at>tarkon.ee )



"Falcon Art" <falconart6@sympatico.ca> wrote in message
news:EW6Ne.20416$kz6.1048597@news20.bellglobal.com...
> 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" <arvilaanemets@hot.ee> wrote in message
> news:uXps%23WCpFHA.3364@tk2msftngp13.phx.gbl...
> 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" <falconart6@sympatico.ca> wrote in message
> news:D81Ne.16988$kz6.987865@news20.bellglobal.com...
>> 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" <garbage@hot.ee> wrote in message
>> news:O$Obb78oFHA.2080@TK2MSFTNGP14.phx.gbl...
>> 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" <falconart6@sympatico.ca> wrote in message
>> news:2QVMe.12295$7R.676080@news20.bellglobal.com...
>> > 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
>> >
>>
>>
>>
>
>
>