First, you could drop the =concatenate() function and use the & operand. It'll
save you typing and won't add a level of nesting (depending on what you're
doing, this could be important).
=IF(B5671="","",HYPERLINK($B$1&LEFT(C5671,3)&"000-"
&LEFT(C5671,3)&"999"&"\"&$C5671&".pdf",$C5671))
Second, you'll need a macro (a user defined function) that would return
true/false depending on the existence of a file.
Option Explicit
Function FileExists(myStr As String) As Boolean
Application.Volatile
Dim TestStr As String
TestStr = ""
On Error Resume Next
TestStr = Dir(myStr)
On Error GoTo 0
If TestStr = "" Then
FileExists = False
Else
FileExists = True
End If
End Function
And instead of building that string twice (once to check for the existence and
once to actually link to it), I'd use an intermediate cell that did the
concatenation (using the & operator).
(Maybe in column D???)
=$B$1&LEFT(C5671,3)&"000-"&LEFT(C5671,3)&"999"&"\"&$C5671&".pdf"
Then my =hyperlink() formula becomes:
=IF(B5671="","",if(fileexists(d5671)=false,"",hyperlink(d5671,$C5671)))
I could replace d5671 with that long formula in both spots, but it gets ugly to
me...
If you're new to macros:
Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html
David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm
(General, Regular and Standard modules all describe the same thing.)
Short course:
Open your workbook.
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)
right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side
==============
Ps. The function is volatile to check to see if that file exists each time
excel recalculates. If you have lots calls to this function, you may not want
to slow down your calculation.
But then if you add/delete the file while this file is open, your formula may
not work the way you want. And it could be a recalculation behind the truth of
the existence of the file!
=======
Another option would be to put a button from the Forms Toolbar on the worksheet
(and freeze panes so that it's always visible.
Then the button could have a macro assigned to try to hyperlink to the file that
the activecell contains.
If you want to try that:
Option Explicit
Sub LinkToMyFile()
Dim TestStr As String
TestStr = ""
On Error Resume Next
TestStr = Dir(ActiveCell.Value)
On Error GoTo 0
If TestStr = "" Then
Beep 'doesn't exist
Else
ThisWorkbook.FollowHyperlink Address:=ActiveCell.Value
End If
End Sub
After you show the Forms toolbar and place the button, just rightclick on that
button and choose Assign macro.
pps. Don't use the commandbutton from the Control toolbox toolbar. The code
would be in a different location and be slightly different.
