Hyperlink help

[philcud]

I have an array formula that creates a hyperlink:-

=IF(K24=0,"",HYPERLINK("[inventory.xls]Inventory!a"&MATCH($A$1&$A24&
$B24,Inventory.xls!$A$2:$A$10000&Inventory.xls!$D$2:$D
$10000&Inventory.xls!$E$2:$E$10000,0)+1,"[inventory.xls]Inventory!
a"&MATCH($A$1&$A24&$B24,Inventory.xls!$A$2:$A$10000&Inventory.xls!$D
$2:$D$10000&Inventory.xls!$E$2:$E$10000,0)+1))

this works and creates the hyperlink:-
[Inventory.xls]Inventory!a1

this works fine, but when the workbook inventory.xls is closed,
nothing at all works when i clickon the link??
any ideas?

 

[Dave Peterson]

Include the path???

I have an array formula that creates a hyperlink:-

=IF(K24=0,"",HYPERLINK("[inventory.xls]Inventory!a"&MATCH($A$1&$A24&
$B24,Inventory.xls!$A$2:$A$10000&Inventory.xls!$D$2:$D
$10000&Inventory.xls!$E$2:$E$10000,0)+1,"[inventory.xls]Inventory!
a"&MATCH($A$1&$A24&$B24,Inventory.xls!$A$2:$A$10000&Inventory.xls!$D
$2:$D$10000&Inventory.xls!$E$2:$E$10000,0)+1))

this works and creates the hyperlink:-
[Inventory.xls]Inventory!a1

this works fine, but when the workbook inventory.xls is closed,
nothing at all works when i clickon the link??
any ideas?

 

[Dave Peterson]

In fact, I'd think about separating this longer formula into a couple of cells.

I have an array formula that creates a hyperlink:-

=IF(K24=0,"",HYPERLINK("[inventory.xls]Inventory!a"&MATCH($A$1&$A24&
$B24,Inventory.xls!$A$2:$A$10000&Inventory.xls!$D$2:$D
$10000&Inventory.xls!$E$2:$E$10000,0)+1,"[inventory.xls]Inventory!
a"&MATCH($A$1&$A24&$B24,Inventory.xls!$A$2:$A$10000&Inventory.xls!$D
$2:$D$10000&Inventory.xls!$E$2:$E$10000,0)+1))

this works and creates the hyperlink:-
[Inventory.xls]Inventory!a1

this works fine, but when the workbook inventory.xls is closed,
nothing at all works when i clickon the link??
any ideas?

 

[philcud]

Include the path???

I have an array formula that creates a hyperlink:-

=IF(K24=0,"",HYPERLINK("[inventory.xls]Inventory!a"&MATCH($A$1&$A24&
$B24,Inventory.xls!$A$2:$A$10000&Inventory.xls!$D$2:$D
$10000&Inventory.xls!$E$2:$E$10000,0)+1,"[inventory.xls]Inventory!
a"&MATCH($A$1&$A24&$B24,Inventory.xls!$A$2:$A$10000&Inventory.xls!$D
$2:$D$10000&Inventory.xls!$E$2:$E$10000,0)+1))

this works and creates the hyperlink:-
[Inventory.xls]Inventory!a1

this works fine, but when the workbook inventory.xls is closed,
nothing at all works when i clickon the link??
any ideas?

dave - i need to try and keep the formulae in as few cells as possible
for sake of neatness. the hyperlink from the formula works perfectly
with the linked spreadsheet open, (the link looks like -
[inventory.xls]Inventory!a8), but when i close inventory and click on
link nothing at all happens - no error message or dialogue box..
inventory.xls is in the same folder, so i didn't think i needed full
path?

 

[Dave Peterson]

If the workbook is closed and you don't specify the entire path, how would excel
even know where to look.

Try this:
Open inventory.xls
select A1 of the first sheet
edit|copy
Create a new workbook

In A1 of the first worksheet in that new workbook:
Edit|paste special|paste link

Notice the formula. It doesn't include the drive/path.

Now close inventory.xls and notice what happens to that formula. Excel needs
some hint at where to find that file.

You could have inventory.xls in every folder of every drive you're connected
to--and even on every shared drive that you have access to.

Include the path???

I have an array formula that creates a hyperlink:-

=IF(K24=0,"",HYPERLINK("[inventory.xls]Inventory!a"&MATCH($A$1&$A24&
$B24,Inventory.xls!$A$2:$A$10000&Inventory.xls!$D$2:$D
$10000&Inventory.xls!$E$2:$E$10000,0)+1,"[inventory.xls]Inventory!
a"&MATCH($A$1&$A24&$B24,Inventory.xls!$A$2:$A$10000&Inventory.xls!$D
$2:$D$10000&Inventory.xls!$E$2:$E$10000,0)+1))

this works and creates the hyperlink:-
[Inventory.xls]Inventory!a1

this works fine, but when the workbook inventory.xls is closed,
nothing at all works when i clickon the link??
any ideas?

dave - i need to try and keep the formulae in as few cells as possible
for sake of neatness. the hyperlink from the formula works perfectly
with the linked spreadsheet open, (the link looks like -
[inventory.xls]Inventory!a8), but when i close inventory and click on
link nothing at all happens - no error message or dialogue box..
inventory.xls is in the same folder, so i didn't think i needed full
path?