Make a hyperlink with variable arguments based on cell values?

Question

I've got a question regarding the HYPERLINK() function in Excel 2013.

I've got a list in column A, containing roughly 1600 filenames. From this list i filter our all the files in the following format '000000_11_22 NAB.xlsm' and place them in column B.

Now what I would like to do is have a variable hyperlink path, where different folders are taken from the filename, which basically looks like:

=HYPERLINK(P:\Data\(LEFT(B1;3)\(Links(B1;6)\Productmap\(B1);"Open file").

This function doesn't work, but I hope it illustrates what I'm trying to do.

After I get this to work, I want to copy this formula for the entire range of 1600 files.

How can I solve this?

Answer 1

Correct syntax is as below, however I don't know what Links function should do in your example.

=HYPERLINK("P:\Data\" & LEFT(B1;3) & "\" & Links(B1;6) & "\Productmap\" & B1;"Open file")

After getting your formula to work it should be easy to fill it down for all the other rows.