SOLVED

Need formula help for inserting multiple hyperlinks

Copper Contributor

I am trying to to insert multiple hyperlinks into a spreadsheet and keep getting the message cannot open the specified file.

 

Formula: =HYPERLINK("C:\<Users\Maria\Documents\Custom Office Templates\Documents\Certificate of Liability Insurance>"&A1&"1901 Inc.pdf")

 

Folder Path: C:\Users\Maria\Documents\Custom Office Templates\Documents\Certificate of Liability Insurance

 

File Name: 1901 Inc.pdf 

4 Replies
best response confirmed by JKangas5 (Copper Contributor)
Solution

@JKangas5 Try stepping through the formula with the Formula Evaluation tool. Does the concatenation of the cells result in the correct path? Are you maybe missing a "\" before the file name? Does the file exist in the location?

 

The HYPERLINK() function uses two parameters: The first one is the link, the second one is the friendly text that the user clicks on. Your formula doesn't seem to have two parameters.

 

What's in cell A1? 

 

Try 

 

HYPERLINK("C:\Users\Maria\Documents\Custom Office Templates\Documents\Certificate of Liability Insurance\1901 Inc.pdf","click here for file")

 

IF that doesn't help, please post a screenshot with more details.

 

@JKangas5 I strongly recommend you not put direct file path into any formula because if you ever need to change it, it is a pain to find and replace.

Instead, please do the following:

1. Create a new Tab called "Help" which contains all your file paths you want to link to (e.g. the header A1 is File path (must include file name and file format), B1 is Friendly name for each file, A2 is your 1st file path C:\Users\Maria\Documents\Custom Office Templates\Documents\Certificate of Liability Insurance\1901 Inc.pdf, B2 is 1901 Inc) and so on.

2. On your main Tab, for example say in cell A2 of Tab Main, use the following formula:

=HYPERLINK(INDIRECT("'Help'!A2"),INDIRECT("'Help'!B2")) to link to your file with the file path listed in cell A2 of Tab Help, and you will see its Friendly name "1901 Inc" shows as a hyperlink

 

Do the same with any filepath in cell A3 with its friendly name in cell B3 of Tab Help

=HYPERLINK(INDIRECT("'Help'!A3"),INDIRECT("'Help'!B3"))

 

Thank you for your help!
Thank you for your help! This worked for me.
1 best response

Accepted Solutions
best response confirmed by JKangas5 (Copper Contributor)
Solution

@JKangas5 Try stepping through the formula with the Formula Evaluation tool. Does the concatenation of the cells result in the correct path? Are you maybe missing a "\" before the file name? Does the file exist in the location?

 

The HYPERLINK() function uses two parameters: The first one is the link, the second one is the friendly text that the user clicks on. Your formula doesn't seem to have two parameters.

 

What's in cell A1? 

 

Try 

 

HYPERLINK("C:\Users\Maria\Documents\Custom Office Templates\Documents\Certificate of Liability Insurance\1901 Inc.pdf","click here for file")

 

IF that doesn't help, please post a screenshot with more details.

 

View solution in original post