CONCATINATION within HYPERLINK function

[Gunkie]

I'm building a project tracking tool based around Excel for a client.
I'm trying to make the tool as foolproof as possible because a new
administrative person will be entering quite a bit of data initially.
I have commentary files (.txt) which I want to link to and I want to
have the ability to create the hyperlinks for each record as easily as
possible (i.e. copy cell).

My intent was to simply use the CONCATENATE function within the
HYPERLINK function to create links to files automatically. I just
learned that this cannot be done in a simple manner such as:

=HYPERLINK(""docs\"&B3&"-"&C3&".txt"", "B3&"-"&C3")

Obviously, the multiple double quotes kill this attempt. Is there a
way, a la PERL (\ preceeding the character), to disable character
function in an excel formula? And if there is, would it work in this
situation?

Jump to the chase: Does it look like I'll need to build a macro for
this? A complex VB application? Just handhold the administrative
person :0 ?

Thanks!

Scott Ghiz
www.SGSystems.com

 

[Dave Peterson]

It looks like you might want to use:

=HYPERLINK("docs\"&B3&"-"&C3&".txt", B3&"-"&C3)

Although I would fully qualify the location.

=HYPERLINK("c:\my docs\docs\"&B3&"-"&C3&".txt", B3&"-"&C3)

If this doesn't help, what are in B3 and C3 and what should the link's real
address.

 

[Gunkie]

Dave,

Thanks for the help!

Both B3 and C3 are formatted 'text' and have (sans quotes) 'Intel' and
'UProcessor' (examples, the data I'm working with is held under an NDA
agreement).

The relative path to the text file used for project commentary is:

docs\Intel-UProcessor.txt

What I want to do is have the links named and pointed to the correct
location everytime I drag a line down (copy) to create an area to input
more information.

I'm thinking that I'll need to build a VB app to do this.

If you, or anyone else can think of anything, please post it. I'm
going to begin building a VB application after I finish this beer I'm
drinking Thanks again for your help!

Scott Ghiz
www.SGSystems.com

 

[Gunkie]

Dave (& everyone else),

I screwed up you idea the first time I tried it. Your method works (or
maybe it was the beer?)!! definately your method was the way to go.

I omitted the surrounding double quotes (around the path and around the
link lable) and the concatenation worked very well.

Thanks again!

Scott Ghiz
www.SGSystems.com

 

[Dave Peterson]

Glad you got it working.

Dave (& everyone else),

I screwed up you idea the first time I tried it. Your method works (or
maybe it was the beer?)!! definately your method was the way to go.

I omitted the surrounding double quotes (around the path and around the
link lable) and the concatenation worked very well.

Thanks again!

Scott Ghiz
www.SGSystems.com