Hyperlink checker

[monicandave]

Good afternoon,

I calculate a series of hyperlinks in a column, only some of which refer to actual files on my computer (not on the Internet). I would like to test for the existence of the file and use the result in an if statement, and display the link if it exists, or leave the cell displaying blank if it does not. Ideally, it should test on opening the spreadsheet or when a control button is clicked.

I have spent days working through various suggestions that I have found, but nothing really fits what I'm looking for.

I will appreciate any help given.

 

[rakesh seebaruth]

=IF(ISNUMBER(MATCH(A1,DIR("C:\My Files\"),0)),HYPERLINK("C:\My Files\" & A1), "")

 

[Fluff]

@rakesh seebaruth what is the DIR function?

 

[rakesh seebaruth]

the DIR FUNCTION returns a string representing the name of a file or directory that matches a specified pattern or file attribute. In this formula, DIR("C:\My Files\") returns the name of a file in the specified directory path "C:\My Files".

 

[Fluff]

What version of Excel is this in? AFAIK there is not (and never has been) a DIR function in Excel.