Create column in list with link to open document in document library

Question

I have created a custom list in SharePoint 2013 (Office 365) using the Import Spreadsheet App. This has provided me with the columns (Project Code, Title, Author, Medium, Quarter, hyperlink, etc.) and data I need to search by, however, now I need to create a new column which will link to the relevant document in my document library. I have tried using a lookup field but this only provides me with folder names and opens the folder location not the document itself.

I realize I can add columns within the document library however I have thousands of records already entered in my spreadsheet which is why I went the Import Spreadsheet App route.

Should I be looking at different document library structures (i.e. document sets) or is this something that can be solved using a workflow? I have read several forums and can't seem to find a solution to this.

Any advice would be much appreciated.

Thanks!

Answer 1

If your existing hyperlink field is the the URL to your document you can indeed add a Calculated Column.

You have to set it to datatype=Number otherwise the Column will just display your HTML code as Text

="<a href='" & Title & "' target='_blank'> " & hyperlink & "</a>"

Note: your hyperlink fields must be a Single Line Of Text, as a Calculated Formula can not access a HyperLink/Picture field.

Answer 2

If you know the URL of the document, you can use a calculated column to combine a url and the item name.

The formula would be:

="<a href='"&%URL_Field_Name%&"'> "&[%field_name%]&"</a>"