Hyperlink does not work

[user125]

Hi all,

I have a simple question but I can't seem to find a solution to it. I have this formula in one of my cells =HYPERLINK("\\networkdrive\123\ABCD\Batch\WER\Export\"&C53&"\WER Batch"). Cell C53 consists of a date that changes everyday. However, when I click on the link it only takes me till folder "Export". Essentially, when I click on the link it should open "WER Batch" folder. Any help would be appreciated!

 

[jasonb75]

It is most likely looking for a folder that matches the date serial number rather than the actual date. You need to use \"&TEXT(C53,"dd/mm/yyyy")&"\ changing the bold part to the correct format for the folder name.

 

[user125]

It is most likely looking for a folder that matches the date serial number rather than the actual date. You need to use \"&TEXT(C53,"dd/mm/yyyy")&"\ changing the bold part to the correct format for the folder name.

Thanks for your reply Jason. Still facing the same issue. Link only takes me till "Export" folder.

 

[jasonb75]

What is in C53 and what is the full path of the folder?

If you remove HYPERLINK from the formula does the resulting text string show the correct path?

 

[user125]

C53 has the formula "=TEXT(ReportDate,"DDMMMYYYY")" where Report Date is a cell in a different worksheet in the same workbook. Hence cell C53 populates 29May2020. The full path of the folder is \\networkdrive\123\ABCD\Batch\WER\Export\29May2020\WER Batch.

If I remove the formula and substitute \"&TEXT(C53,"dd/mm/yyyy")&"\ with 29May2020, it takes me to the WER Batch folder which is what I need.

 

[jasonb75]

If C53 already has a formula with the TEXT function then using the text function again in the HYPERLINK formula will do nothing anyway.

Does it work if you skip out C53 and try it as

=HYPERLINK("\\networkdrive\123\ABCD\Batch\WER\Export\"&TEXT(ReportDate,"DDMMMYYYY")&"\WER Batch")

I'm not understanding why it is opening the wrong folder, if the path was invalid then you should get a popup warning for an invalid file.

If you drop the last part, does it open the correct date folder?

 

[user125]

Made the change and it still takes me till the "Export" folder. I also tried another test where I entered \\networkdrive\123\ABCD\Batch\WER\Export\29May2020\WER Batch in cell A1 and =HYPERLINK("\\networkdrive\123\ABCD\Batch\WER\Export\"&TEXT(ReportDate,"DDMMMYYYY")&"\WER Batch") in cell A2 and then used an IF formula in cell A3 =IF(A1=A2,TRUE,FALSE) and the formula populated "TRUE" as the outcome.

At this point, I'm not sure what could be wrong with the HYPERLINK formula.

 

[jasonb75]

Did you try dropping the last part to see if it goes to Export or 29May2020?

=HYPERLINK("\\networkdrive\123\ABCD\Batch\WER\Export\"&C53)

You could also try &"\" at the end, for me everything works with or without but no harm in trying all options.

 

[user125]

Hi Jason,

Thanks for all your help. I tried various methods and one worked. I simply removed the hyperlink and added it back and it seemed to work fine after. Maybe just a technical glitch. Thanks again.

 

[jasonb75]

I might be reading that wrong, but I'm seeing that you had a hyperlink formula and a direct hyperlink from the right click menu in the same cell. If that is the case then they were probably conflicting with each other. I'm not sure which (if either) would have priority in that situation.

Regardless of that, glad you got it working