Using Relative Hyperlinks

How would one set up relative hyperlinks in Excel? For example, if the links
refer to a file system directory, so that the whole hyperlink would be <base
directory>/<variable part of filename>, how could one set things up so that
if the <base directory> were to change, one could either change one value or
change all of the hyperlinks at one shot?

Let's say that <base directory> = "c:\my files\html base\" and <variable
....> = "html_file1.htm", "html_file2.htm", etc. How would I change the
"c:\my files\html base\" to "c:\my files\other html base\" with one edit?
And how do I set up relative hyperlinks in the spreadsheet?

I have very minimal Excel knowledge/experience. So please keep things real
simple for me, if possible.
--

Chaim

I'm not sure if this option is available in all versions of excel. It's there
in xl2002+, though.

Look at file|properties|summary tab
Do you see "hyperlink Base" at the bottom of that dialog?

If you do, then you can search excel's help for "hyperlink base" to see how it
works.

And if you need to change that base, you could use code like:

Thisworkbook.BuiltinDocumentProperties("Hyperlink Base") _
= "whateveryouwanthere"

or

ThisWorkbook.BuiltinDocumentProperties("Hyperlink Base") = ThisWorkbook.Path

If you put it in a general module and name the procedure Auto_open(), it'll run
each time you open the file.

Option Explicit
sub Auto_open()
Thisworkbook.BuiltinDocumentProperties("Hyperlink Base") = ThisWorkbook.Path
end sub

I don't use this kind of hyperlink very often.

There is an =hyperlink() worksheet function that may work for you.

You can put parts of the string in other cells and build up the link:

For example, you could put:
file:////c:\my files\
in A1

Then put the variable names in A2:Axxx.

Then in B2, put:
=hyperlink($a$1&a2)
and drag down the column.

Changing $a$1 would adjust all the =hyperlink() formulas.

Chaim wrote:
>
> How would one set up relative hyperlinks in Excel? For example, if the links
> refer to a file system directory, so that the whole hyperlink would be <base
> directory>/<variable part of filename>, how could one set things up so that
> if the <base directory> were to change, one could either change one value or
> change all of the hyperlinks at one shot?
>
> Let's say that <base directory> = "c:\my files\html base\" and <variable
> ...> = "html_file1.htm", "html_file2.htm", etc. How would I change the
> "c:\my files\html base\" to "c:\my files\other html base\" with one edit?
> And how do I set up relative hyperlinks in the spreadsheet?
>
> I have very minimal Excel knowledge/experience. So please keep things real
> simple for me, if possible.
> --
>
> Chaim

--

Dave Peterson

Dave,

Thanks for the reply.

The office I'm in has Excel 2000 and it has the Hyperlink Base property.
However, it appears that this is used with the Hyperlink Editor. I'd rather
not go with an option that requires code; I'd do it, but the other users are
not near capable/competent.

The hyperlink() function idea worked great. One thing that I couldn't
understand though, was your comment about "and drag down the column." I put
the "=hyperlink(etc.)" in just one cell? And then what does it mean to 'drag
down the column'? I know you mean a mouse drag, but what should I see? Will
this copy the formula to each cell? Or will this cause it to be applied to
each cell?

Sorry. As I said, I have minimal Excel knowledge/experience.

--

Chaim


"Dave Peterson" <[email protected]> wrote in message
news:[email protected]...
> I'm not sure if this option is available in all versions of excel. It's
there
> in xl2002+, though.
>
> Look at file|properties|summary tab
> Do you see "hyperlink Base" at the bottom of that dialog?
>
> If you do, then you can search excel's help for "hyperlink base" to see
how it
> works.
>
> And if you need to change that base, you could use code like:
>
> Thisworkbook.BuiltinDocumentProperties("Hyperlink Base") _
> = "whateveryouwanthere"
>
> or
>
> ThisWorkbook.BuiltinDocumentProperties("Hyperlink Base") =
ThisWorkbook.Path
>
> If you put it in a general module and name the procedure Auto_open(),
it'll run
> each time you open the file.
>
> Option Explicit
> sub Auto_open()
> Thisworkbook.BuiltinDocumentProperties("Hyperlink Base") =
ThisWorkbook.Path
> end sub
>
> I don't use this kind of hyperlink very often.
>
> There is an =hyperlink() worksheet function that may work for you.
>
> You can put parts of the string in other cells and build up the link:
>
> For example, you could put:
> file:////c:\my files\
> in A1
>
> Then put the variable names in A2:Axxx.
>
> Then in B2, put:
> =hyperlink($a$1&a2)
> and drag down the column.
>
> Changing $a$1 would adjust all the =hyperlink() formulas.
>
> Chaim wrote:
> >
> > How would one set up relative hyperlinks in Excel? For example, if the
links
> > refer to a file system directory, so that the whole hyperlink would be
<base
> > directory>/<variable part of filename>, how could one set things up so
that
> > if the <base directory> were to change, one could either change one
value or
> > change all of the hyperlinks at one shot?
> >
> > Let's say that <base directory> = "c:\my files\html base\" and <variable
> > ...> = "html_file1.htm", "html_file2.htm", etc. How would I change the
> > "c:\my files\html base\" to "c:\my files\other html base\" with one
edit?
> > And how do I set up relative hyperlinks in the spreadsheet?
> >
> > I have very minimal Excel knowledge/experience. So please keep things
real
> > simple for me, if possible.
> > --
> >
> > Chaim
>
> --
>
> Dave Peterson

When you select a cell, do you see a little box in the bottom right corner?

If you don't, you can toggle a setting:
Tools|Options|edit tab|allow cell drag and drop

Then you can click on this little autofill box and drag down the column to copy
what's in that cell (formula or constant).

Debra Dalgleish describes it with pictures at:
http://contextures.com/xlDataEntry01.html#Mouse



Chaim wrote:
>
> Dave,
>
> Thanks for the reply.
>
> The office I'm in has Excel 2000 and it has the Hyperlink Base property.
> However, it appears that this is used with the Hyperlink Editor. I'd rather
> not go with an option that requires code; I'd do it, but the other users are
> not near capable/competent.
>
> The hyperlink() function idea worked great. One thing that I couldn't
> understand though, was your comment about "and drag down the column." I put
> the "=hyperlink(etc.)" in just one cell? And then what does it mean to 'drag
> down the column'? I know you mean a mouse drag, but what should I see? Will
> this copy the formula to each cell? Or will this cause it to be applied to
> each cell?
>
> Sorry. As I said, I have minimal Excel knowledge/experience.
>
> --
>
> Chaim
>
> "Dave Peterson" <[email protected]> wrote in message
> news:[email protected]...
> > I'm not sure if this option is available in all versions of excel. It's
> there
> > in xl2002+, though.
> >
> > Look at file|properties|summary tab
> > Do you see "hyperlink Base" at the bottom of that dialog?
> >
> > If you do, then you can search excel's help for "hyperlink base" to see
> how it
> > works.
> >
> > And if you need to change that base, you could use code like:
> >
> > Thisworkbook.BuiltinDocumentProperties("Hyperlink Base") _
> > = "whateveryouwanthere"
> >
> > or
> >
> > ThisWorkbook.BuiltinDocumentProperties("Hyperlink Base") =
> ThisWorkbook.Path
> >
> > If you put it in a general module and name the procedure Auto_open(),
> it'll run
> > each time you open the file.
> >
> > Option Explicit
> > sub Auto_open()
> > Thisworkbook.BuiltinDocumentProperties("Hyperlink Base") =
> ThisWorkbook.Path
> > end sub
> >
> > I don't use this kind of hyperlink very often.
> >
> > There is an =hyperlink() worksheet function that may work for you.
> >
> > You can put parts of the string in other cells and build up the link:
> >
> > For example, you could put:
> > file:////c:\my files\
> > in A1
> >
> > Then put the variable names in A2:Axxx.
> >
> > Then in B2, put:
> > =hyperlink($a$1&a2)
> > and drag down the column.
> >
> > Changing $a$1 would adjust all the =hyperlink() formulas.
> >
> > Chaim wrote:
> > >
> > > How would one set up relative hyperlinks in Excel? For example, if the
> links
> > > refer to a file system directory, so that the whole hyperlink would be
> <base
> > > directory>/<variable part of filename>, how could one set things up so
> that
> > > if the <base directory> were to change, one could either change one
> value or
> > > change all of the hyperlinks at one shot?
> > >
> > > Let's say that <base directory> = "c:\my files\html base\" and <variable
> > > ...> = "html_file1.htm", "html_file2.htm", etc. How would I change the
> > > "c:\my files\html base\" to "c:\my files\other html base\" with one
> edit?
> > > And how do I set up relative hyperlinks in the spreadsheet?
> > >
> > > I have very minimal Excel knowledge/experience. So please keep things
> real
> > > simple for me, if possible.
> > > --
> > >
> > > Chaim
> >
> > --
> >
> > Dave Peterson

--

Dave Peterson

Additional information on fill handle and use of mouse can be found in
http://www.mvps.org/dmcritchie/excel/fillhand.htm
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Dave Peterson" <[email protected]> wrote in message
> Debra Dalgleish describes it (fill handle) with pictures at:
> http://contextures.com/xlDataEntry01.html#Mouse

Thanks much. That was very useful.

And thanks for following this. Since it dates back to last week, I thought
it likely that this would have dropped off everyone's radar. I'm pleasantly-
and gratefully- surprised.

--

Chaim


"Dave Peterson" <[email protected]> wrote in message
news:[email protected]...
> When you select a cell, do you see a little box in the bottom right
corner?
>
> If you don't, you can toggle a setting:
> Tools|Options|edit tab|allow cell drag and drop
>
> Then you can click on this little autofill box and drag down the column to
copy
> what's in that cell (formula or constant).
>
> Debra Dalgleish describes it with pictures at:
> http://contextures.com/xlDataEntry01.html#Mouse
>
>
>
> Chaim wrote:
> >
> > Dave,
> >
> > Thanks for the reply.
> >
> > The office I'm in has Excel 2000 and it has the Hyperlink Base property.
> > However, it appears that this is used with the Hyperlink Editor. I'd
rather
> > not go with an option that requires code; I'd do it, but the other users
are
> > not near capable/competent.
> >
> > The hyperlink() function idea worked great. One thing that I couldn't
> > understand though, was your comment about "and drag down the column." I
put
> > the "=hyperlink(etc.)" in just one cell? And then what does it mean to
'drag
> > down the column'? I know you mean a mouse drag, but what should I see?
Will
> > this copy the formula to each cell? Or will this cause it to be applied
to
> > each cell?
> >
> > Sorry. As I said, I have minimal Excel knowledge/experience.
> >
> > --
> >
> > Chaim
> >
> > "Dave Peterson" <[email protected]> wrote in message
> > news:[email protected]...
> > > I'm not sure if this option is available in all versions of excel.
It's
> > there
> > > in xl2002+, though.
> > >
> > > Look at file|properties|summary tab
> > > Do you see "hyperlink Base" at the bottom of that dialog?
> > >
> > > If you do, then you can search excel's help for "hyperlink base" to
see
> > how it
> > > works.
> > >
> > > And if you need to change that base, you could use code like:
> > >
> > > Thisworkbook.BuiltinDocumentProperties("Hyperlink Base") _
> > > = "whateveryouwanthere"
> > >
> > > or
> > >
> > > ThisWorkbook.BuiltinDocumentProperties("Hyperlink Base") =
> > ThisWorkbook.Path
> > >
> > > If you put it in a general module and name the procedure Auto_open(),
> > it'll run
> > > each time you open the file.
> > >
> > > Option Explicit
> > > sub Auto_open()
> > > Thisworkbook.BuiltinDocumentProperties("Hyperlink Base") =
> > ThisWorkbook.Path
> > > end sub
> > >
> > > I don't use this kind of hyperlink very often.
> > >
> > > There is an =hyperlink() worksheet function that may work for you.
> > >
> > > You can put parts of the string in other cells and build up the link:
> > >
> > > For example, you could put:
> > > file:////c:\my files\
> > > in A1
> > >
> > > Then put the variable names in A2:Axxx.
> > >
> > > Then in B2, put:
> > > =hyperlink($a$1&a2)
> > > and drag down the column.
> > >
> > > Changing $a$1 would adjust all the =hyperlink() formulas.
> > >
> > > Chaim wrote:
> > > >
> > > > How would one set up relative hyperlinks in Excel? For example, if
the
> > links
> > > > refer to a file system directory, so that the whole hyperlink would
be
> > <base
> > > > directory>/<variable part of filename>, how could one set things up
so
> > that
> > > > if the <base directory> were to change, one could either change one
> > value or
> > > > change all of the hyperlinks at one shot?
> > > >
> > > > Let's say that <base directory> = "c:\my files\html base\" and
<variable
> > > > ...> = "html_file1.htm", "html_file2.htm", etc. How would I change
the
> > > > "c:\my files\html base\" to "c:\my files\other html base\" with one
> > edit?
> > > > And how do I set up relative hyperlinks in the spreadsheet?
> > > >
> > > > I have very minimal Excel knowledge/experience. So please keep
things
> > real
> > > > simple for me, if possible.
> > > > --
> > > >
> > > > Chaim
> > >
> > > --
> > >
> > > Dave Peterson
>
> --
>
> Dave Peterson