Automating Links

[dwpfreak]

Dear all, I have arranged a sheet where a user just has to enter their server number and their folder name (which is their pay number).

Problem is i add the string of text together which exactly replicates the link string but it doesn't show the value from that sheet.

for example ='c:\documents and settings\my documents\linktest\123456\[link.xls]sheet1!a1

This is how the actual link looks, but when i combine columns of text into an exact replica of the above, No Link????

where am I going wrong and how do i get my combined text string to act as the link?

I hope someone out there knows what i am talking about and how to help?

thanks

 

[mikerickson]

Have you tried it with a single-quote before the ! (or is it after). I don't use that syntax very often, but I recall that it needs matching single-quotes.

 

[dwpfreak]

yes I tried that, thanks for that suggestion, any other ideas or should i try a different approach?

 

[mikerickson]

How are you using that Link String?

 

[dwpfreak]

well the idea is this,

in column A there will be the beginning of a normal link.

In column B the person doing the linking enters their server ID, in column C they enter the folder Name, then a cell using =(a1&b1&c1) formats the string as the link. But all it is shwoing is the string of formula.

It is identical to the actual link I put in, but doesn't work.

Hence why baffled

if you know what I am missing please help

 

[mikerickson]

The reason its not calculating is that the cell holds text, not a formula. Excel was instructed to show those characters, not the interpretation of them as a formula.

The INDIRECT function might help.

In my testing, if B1 held Macintosh HD:Users:merickson:Desktop:[BookA.xls]Sheet1
and B2 held [BookA.xls]Sheet1

And A1, the formula =INDIRECT(CHAR(39)&B1&CHAR(39)&"!$A$1")
and A2 , =INDIRECT(CHAR(39)&B2&CHAR(39)&"!$A$1")

Both A1 and A2 returned the value from BookA.xls, but only when it was open.

I don't think that INDIRECT can return a value from a closed workbook.