Excel formula for creating hyperlinks using concatenation
PKittie
-
gbinforme Posted messages 14930 Registration date Status Contributor Last intervention -
gbinforme Posted messages 14930 Registration date Status Contributor Last intervention -
Hello everyone,
This is my first time posting on CCM, so hello to all! (I'm using Excel 2010 in English)
I have an Excel table consisting of 2 columns (A and B);
I would like to create a hyperlink for each row in column B, a link that would automatically consist of 4 parts (use concatenate function?)
1: the file location prefix
2: the content of the current cell (row n, column B)
3: the content of the cell in column A (row n, column A)
and lastly: ".pdf" because it will only be PDF files.
PS: each of these cells (column B) will be named by a date and the PDF files will be named "2015-MM-DD - EXAMPLE.PDF"
I searched a bit online and tried this formula but it was a failed attempt...
=HYPERLINK(CONCATENATE("File:///\\*****\*****\*****o\2015\"&E89&" - ";D89;".pdf");"21/09/2015")
There you go, I hope I was clear... And if this topic has already been covered, I apologize... But I couldn't find it!
Thank you!
Configuration: Windows 7 / Firefox 40.0
This is my first time posting on CCM, so hello to all! (I'm using Excel 2010 in English)
I have an Excel table consisting of 2 columns (A and B);
I would like to create a hyperlink for each row in column B, a link that would automatically consist of 4 parts (use concatenate function?)
1: the file location prefix
2: the content of the current cell (row n, column B)
3: the content of the cell in column A (row n, column A)
and lastly: ".pdf" because it will only be PDF files.
PS: each of these cells (column B) will be named by a date and the PDF files will be named "2015-MM-DD - EXAMPLE.PDF"
I searched a bit online and tried this formula but it was a failed attempt...
=HYPERLINK(CONCATENATE("File:///\\*****\*****\*****o\2015\"&E89&" - ";D89;".pdf");"21/09/2015")
There you go, I hope I was clear... And if this topic has already been covered, I apologize... But I couldn't find it!
Thank you!
Configuration: Windows 7 / Firefox 40.0
1 answer
Hello,
if you have 2 columns of data to use, you should not put your formula in the used column B but in C.
This should work with your data in A1 and B1.
Always zen
Perfection is achieved, not when there is nothing more to add, but when there is nothing left to take away. Antoine de Saint-Exupéry
if you have 2 columns of data to use, you should not put your formula in the used column B but in C.
=HYPERLINK("file:///D:\***\***\***\"&B1&A1&".pdf";"21/09/2015") This should work with your data in A1 and B1.
Always zen
Perfection is achieved, not when there is nothing more to add, but when there is nothing left to take away. Antoine de Saint-Exupéry