Links to closed files using a relative file path

[AKarim]

Hi to all

I have an issue getting the value of a cell from a file to an other :
I have created the files and docs as follow :
Portfolio
Portfolio.xls
Site 1
Proj1
ExcelProj.xls
Proj2
....
Site 2
...

In ExcelProj.xls I have named the cell L1 : _Name and M1 : _Fname
In portfolio I'm trying to get the information in C2 the value of _Name and Fname by doing some concatenations :

Portfolio looks like :
cell A B C D
1 Site Proj Name fname
2 Site1 Proj1 =concatenate("'.\relativepath\";$A2;"\";$B2;"\Excel";$B2;".xls'!_";C$1")
3 Site1 Proj2 ... =concatenate("'.\relativepath\";$A3;"\";$B3;"\Excel";$B3;".xls'!_";D$1")
4 ...
..
n Site2 Proj1

The aim is to use the function
='path\filename.xls'!_Beneficiarios but by constructing the address

thanks for any help on turning the excel to use the relative path (relative to the portfolio file position and the way to construct the formula.

Karim

 

[SkipVought]

Hi,

However, all you have is a STRING!!!

Try wrapping that in the INDIRECT() function like...
[tt]
=INDIRECT(concatenate("'.\relativepath\";$A2;"\";$B2;"\Excel";$B2;".xls'!_";C$1"))
[/tt]


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[AKarim]

Hi Skip yep I tried this out, but don't know why it doesnt work

Can't remember if I have to insert specific characters in front of the ' in the string ? ...$B2;".xls''!_...

 

[AKarim]

In fact if I put
='C:\completepath\Site1\Proj1\ExcelProj1.xls'!_Name this works
If I change into
='.\relativepath\Site1\Proj1\ExcelProj1.xls'!_Name doesnt works (opens the windows to choose the xls file for the ref)

and when I try
indirect(concatenate("'C:\completepath\Site1\Proj1\ExcelProj1.xls'!_Name")) it doesn't work either #REF

 

[SkipVought]

what do your references resolve to in the cell display, NOT THE FORMULA!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[GlennUK]

You say

and when I try
indirect(concatenate("'C:\completepath\Site1\Proj1\ExcelProj1.xls'!_Name")) it doesn't work either #REF

... did you have ExcelProj1.xls open at the same time? Or was it closed?

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[AKarim]

hi,

Glenn, you are right it actually works with the file opened, not when it's closed, but I need it to be closed :-( (because the update will be digging in 15 other files, so I don't want to have to open the 15 files each time)

Skip, sorry I don't catch your question

 

[AKarim]

Just to update, found an alternate solution (requiering additionnal functions)
you can use an Excel addin :

http://xcell05.free.fr/morefunc/english/

works perfectly !

thanks to you guys for having taken time to answer !
if any solution without this tool, still welcome of course, but I guess shouldn't be working otherwise this dev wouldn't have been done !
cheers
Karim

 

[GlennUK]

Hi Karim,

can you download from that link? I can't seem to at the moment. ( I don't need to, as I already have MOREFUNC, but thought I'd check that the link was working )

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[AKarim]

Hi Glenn no you are right the link isn't working, a friend sent me the link and the code, so I hadn't tried it.
I have the code but have issues on the site too
Cheers K.

 

[GlennUK]

This link for the actual download seems to work, for anyone that wants it:

http://xcell05.free.fr/downloads/Morefunc.zip

Cheers, Glenn.

Beauty is in the eye of the beerholder.