I am using the Hyperlink function along with the concatenate function to
create a link. I would like to only see the link if the file actually
exists. Is this possible?

Here is an example of my formula so far:

=IF(B5671="","",(HYPERLINK(CONCATENATE($B$1,CONCAT ENATE(LEFT(C5671,3),"000-",CONCATENATE(LEFT(C5671,3),"999","\",$C5671,".pdf "))),(CONCATENATE($C5671)))))

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,"",hype rlink(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 he
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.

bevchapman wrote:

I am using the Hyperlink function along with the concatenate function to
create a link. I would like to only see the link if the file actually
exists. Is this possible?

Here is an example of my formula so far:

=IF(B5671="","",(HYPERLINK(CONCATENATE($B$1,CONCAT ENATE(LEFT(C5671,3),"000-",CONCATENATE(LEFT(C5671,3),"999","\",$C5671,".pdf "))),(CONCATENATE($C5671)))))

--

Dave Peterson

Does this formula work?
I would have thought if should have been something like:
=IF(B5671="","",(HYPERLINK(CONCATENATE($B$1,CONCAT ENATE(LEFT(C5671,3)&"000-"&CONCATENATE(LEFT(C5671,3)&"999"&"\"&$C5671&".pdf "))),$C5671))

"bevchapman" wrote:

I am using the Hyperlink function along with the concatenate function to
create a link. I would like to only see the link if the file actually
exists. Is this possible?

Here is an example of my formula so far:

=IF(B5671="","",(HYPERLINK(CONCATENATE($B$1,CONCAT ENATE(LEFT(C5671,3),"000-",CONCATENATE(LEFT(C5671,3),"999","\",$C5671,".pdf "))),(CONCATENATE($C5671)))))

The formula I posted does work. Just want to add to it so that it will only
create the link if the file exists.

"Ron@Buy" wrote:

Does this formula work?
I would have thought if should have been something like:
=IF(B5671="","",(HYPERLINK(CONCATENATE($B$1,CONCAT ENATE(LEFT(C5671,3)&"000-"&CONCATENATE(LEFT(C5671,3)&"999"&"\"&$C5671&".pdf "))),$C5671))

"bevchapman" wrote:

I am using the Hyperlink function along with the concatenate function to
create a link. I would like to only see the link if the file actually
exists. Is this possible?

Here is an example of my formula so far:

=IF(B5671="","",(HYPERLINK(CONCATENATE($B$1,CONCAT ENATE(LEFT(C5671,3),"000-",CONCATENATE(LEFT(C5671,3),"999","\",$C5671,".pdf "))),(CONCATENATE($C5671)))))

OK
I don't think you can test for file existence within a formula I think you
will have to use VBA to test for the file, see
http://spreadsheetpage.com/index.php...vba_functions/
for some guidance

"bevchapman" wrote:

The formula I posted does work. Just want to add to it so that it will only
create the link if the file exists.

"Ron@Buy" wrote:

Does this formula work?
I would have thought if should have been something like:
=IF(B5671="","",(HYPERLINK(CONCATENATE($B$1,CONCAT ENATE(LEFT(C5671,3)&"000-"&CONCATENATE(LEFT(C5671,3)&"999"&"\"&$C5671&".pdf "))),$C5671))

"bevchapman" wrote:

I am using the Hyperlink function along with the concatenate function to
create a link. I would like to only see the link if the file actually
exists. Is this possible?

Here is an example of my formula so far:

=IF(B5671="","",(HYPERLINK(CONCATENATE($B$1,CONCAT ENATE(LEFT(C5671,3),"000-",CONCATENATE(LEFT(C5671,3),"999","\",$C5671,".pdf "))),(CONCATENATE($C5671)))))