Reference file name in Excel function

[ajorlov]

Is there an Excel function which can say "If the name of the file I am in is X, then return value Y, otherwise return value Z."?

In other words, =IF([name of file]="Today", "OK", "Not OK")

Thanks

 

[Zack Barresse]

Hi,


You can either use a series of worksheet functions to retreive the filename, e.g. ..


=MID(CELL("filename"),FIND("[",CELL("filename"),1)+1,LEN(CELL("filename"))-FIND("]",CELL("filename"),1)-1)


Or you can write your own UDF. This example would go into a standard module. ..

Option Explicit

Public Function BOOKNAME(Optional celRef As Range) As String
    If Not celRef Is Nothing Then
        BOOKNAME = celRef.Parent.Parent.Name
    Else
        BOOKNAME = Application.Caller.Parent.Parent.Name
    End If
End Function

Note that the two solutions above will appear different. The native formula result will not have a .xls on the end, whilst the UDF function will. For a UDF that does not have that, you could put the option of it in there ...

Option Explicit

Public Function BOOKNAME(Optional celRef As Range, _
    Optional theType As Integer) As String
    Dim myStr As String
    If Not celRef Is Nothing Then
        myStr = celRef.Parent.Parent.Name
        Select Case theType
        '1 = Book1.xls
        '2 = Book1
        Case 1, 0
            BOOKNAME = myStr
        Case 2
            BOOKNAME = Left$(myStr, _
                Len(myStr) - 4)
        End Select
    Else
        myStr = Application.Caller.Parent.Parent.Name
        Select Case theType
        Case 1, 0
            BOOKNAME = myStr
        Case 2
            BOOKNAME = Left$(myStr, Len(myStr) - 4)
        End Select
    End If
End Function

Then you could use something like this ...


=BOOKNAME(,2)

This will give you something like "Book1". Optionally ..

=BOOKNAME(,1)
or
=BOOKNAME(,0)
or
=BOOKNAME()

Will give you "Book1.xls" type results. You can specify other workbooks also with the first (optional) syntax.


HTH