Struggling here and maybe being a bit thick, however had a look at Tom
Ogilvys resonse to a similar question on the 16 August 2006, tried to use the
same principals to no avail.

I have a number of PC's with large number of files that I am trying to tidy
up. I have created worksheets for all the files named on the IP Address. I
wish to have an index function that returns information dependant on 3 fields
in the main page.

I am trying to use Index and Match functions to obtain the information.
The code works fine with the formula in the cell, however with a large
number of PC's I would need to run the machine overnight !

The function I have tried is below

Function GetFileVersion(strFilePath As String, strFileName As String,
strFileType As String, strPCIP As String)

Dim ans As Variant

ans = Evaluate("INDEX('" & strPCIP & "'!$K$1:$K$5000,(MATCH((strFilePath &
strFileName & strFileType), ('" & strPCIP & "'!$A$1:$A$5000&'" & strPCIP &
"'!$B$1:$B$5000&'" & strPCIP & "'!$D$1:$D$5000),0))")

If Not IsError(ans) Then
MsgBox ans
Else
MsgBox "Not found"
End If

GetFileVersion = ans

End Function

Any help or pointers would be greatly appreciated

Regards

Try this

Function GetFileVersion(strFilePath As String, _
strFileName As String, _
strFileType As String, _
strPCIP As String)
Dim ans As Variant
Dim sFormula As String

sFormula = "INDEX('" & strPCIP & "'!$K$1:$K$5000,(MATCH((""" & _
strFilePath & """&""" & strFileName & """&""" & strFileType
& _
"),('" & strPCIP & "'!$A$1:$A$5000&'" & strPCIP & _
"'!$B$1:$B$5000&'" & strPCIP & "'!$D$1:$D$5000),0))"

ans = Evaluate(sFormula)

If Not IsError(ans) Then
GetFileVersion = ans
Else
GetFileVersion = "Not found"
End If

End Function



--
HTH

Bob Phillips

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Alan" wrote in message
...
Struggling here and maybe being a bit thick, however had a look at Tom
Ogilvys resonse to a similar question on the 16 August 2006, tried to use
the
same principals to no avail.

I have a number of PC's with large number of files that I am trying to
tidy
up. I have created worksheets for all the files named on the IP Address. I
wish to have an index function that returns information dependant on 3
fields
in the main page.

I am trying to use Index and Match functions to obtain the information.
The code works fine with the formula in the cell, however with a large
number of PC's I would need to run the machine overnight !

The function I have tried is below

Function GetFileVersion(strFilePath As String, strFileName As String,
strFileType As String, strPCIP As String)

Dim ans As Variant

ans = Evaluate("INDEX('" & strPCIP & "'!$K$1:$K$5000,(MATCH((strFilePath &
strFileName & strFileType), ('" & strPCIP & "'!$A$1:$A$5000&'" & strPCIP &
"'!$B$1:$B$5000&'" & strPCIP & "'!$D$1:$D$5000),0))")

If Not IsError(ans) Then
MsgBox ans
Else
MsgBox "Not found"
End If

GetFileVersion = ans

End Function

Any help or pointers would be greatly appreciated

Regards

Bob

Again many thanks for your response ... however seems i get a negative
result although I know the file is there ...

The code within the cell is

=INDEX('###.##.#.###'!$K$1:$K$5000,MATCH(B7&C11&D1 1,'###.##.#.###'!$A$1:$A$5000&'###.##.#.###'!$B$1: $B$5000&'###.##.#.###'!$D$1:$D$5000,0))

And I get the right info

Any Ideas ???

Thanks

"Bob Phillips" wrote:

Try this

Function GetFileVersion(strFilePath As String, _
strFileName As String, _
strFileType As String, _
strPCIP As String)
Dim ans As Variant
Dim sFormula As String

sFormula = "INDEX('" & strPCIP & "'!$K$1:$K$5000,(MATCH((""" & _
strFilePath & """&""" & strFileName & """&""" & strFileType
& _
"),('" & strPCIP & "'!$A$1:$A$5000&'" & strPCIP & _
"'!$B$1:$B$5000&'" & strPCIP & "'!$D$1:$D$5000),0))"

ans = Evaluate(sFormula)

If Not IsError(ans) Then
GetFileVersion = ans
Else
GetFileVersion = "Not found"
End If

End Function



--
HTH

Bob Phillips

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Alan" wrote in message
...
Struggling here and maybe being a bit thick, however had a look at Tom
Ogilvys resonse to a similar question on the 16 August 2006, tried to use
the
same principals to no avail.

I have a number of PC's with large number of files that I am trying to
tidy
up. I have created worksheets for all the files named on the IP Address. I
wish to have an index function that returns information dependant on 3
fields
in the main page.

I am trying to use Index and Match functions to obtain the information.
The code works fine with the formula in the cell, however with a large
number of PC's I would need to run the machine overnight !

The function I have tried is below

Function GetFileVersion(strFilePath As String, strFileName As String,
strFileType As String, strPCIP As String)

Dim ans As Variant

ans = Evaluate("INDEX('" & strPCIP & "'!$K$1:$K$5000,(MATCH((strFilePath &
strFileName & strFileType), ('" & strPCIP & "'!$A$1:$A$5000&'" & strPCIP &
"'!$B$1:$B$5000&'" & strPCIP & "'!$D$1:$D$5000),0))")

If Not IsError(ans) Then
MsgBox ans
Else
MsgBox "Not found"
End If

GetFileVersion = ans

End Function

Any help or pointers would be greatly appreciated

Regards

Bob

Cancel ... my typing is not what it shoulf be ... syntax and brackets
Once again very much obliged for your help

Regards

"Alan" wrote:

Bob

Again many thanks for your response ... however seems i get a negative
result although I know the file is there ...

The code within the cell is

=INDEX('###.##.#.###'!$K$1:$K$5000,MATCH(B7&C11&D1 1,'###.##.#.###'!$A$1:$A$5000&'###.##.#.###'!$B$1: $B$5000&'###.##.#.###'!$D$1:$D$5000,0))

And I get the right info

Any Ideas ???

Thanks

"Bob Phillips" wrote:

Try this

Function GetFileVersion(strFilePath As String, _
strFileName As String, _
strFileType As String, _
strPCIP As String)
Dim ans As Variant
Dim sFormula As String

sFormula = "INDEX('" & strPCIP & "'!$K$1:$K$5000,(MATCH((""" & _
strFilePath & """&""" & strFileName & """&""" & strFileType
& _
"),('" & strPCIP & "'!$A$1:$A$5000&'" & strPCIP & _
"'!$B$1:$B$5000&'" & strPCIP & "'!$D$1:$D$5000),0))"

ans = Evaluate(sFormula)

If Not IsError(ans) Then
GetFileVersion = ans
Else
GetFileVersion = "Not found"
End If

End Function



--
HTH

Bob Phillips

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Alan" wrote in message
...
Struggling here and maybe being a bit thick, however had a look at Tom
Ogilvys resonse to a similar question on the 16 August 2006, tried to use
the
same principals to no avail.

I have a number of PC's with large number of files that I am trying to
tidy
up. I have created worksheets for all the files named on the IP Address. I
wish to have an index function that returns information dependant on 3
fields
in the main page.

I am trying to use Index and Match functions to obtain the information.
The code works fine with the formula in the cell, however with a large
number of PC's I would need to run the machine overnight !

The function I have tried is below

Function GetFileVersion(strFilePath As String, strFileName As String,
strFileType As String, strPCIP As String)

Dim ans As Variant

ans = Evaluate("INDEX('" & strPCIP & "'!$K$1:$K$5000,(MATCH((strFilePath &
strFileName & strFileType), ('" & strPCIP & "'!$A$1:$A$5000&'" & strPCIP &
"'!$B$1:$B$5000&'" & strPCIP & "'!$D$1:$D$5000),0))")

If Not IsError(ans) Then
MsgBox ans
Else
MsgBox "Not found"
End If

GetFileVersion = ans

End Function

Any help or pointers would be greatly appreciated

Regards