hello,

i try to write a function corresponding to index and match
functions used in excel spreadsheets..

i have sth like this:

Function FindID(c As Variant, x As Integer)

'c - file (ie. [file1.xls]
'x - arkusz (ie. 4

FindID = "=INDEX('" & c & x & "'!R8C3:R8C154,1,MATCH(RC3,'" & c & x & "'!R15C3:R15C154,0))"

End Function

is it possible to build forumula in such a way?

best

Function FindID(c As Variant, x As Integer)

'c - file (ie. [file1.xls]
'x - arkusz (ie. 4

s = "=INDEX('" & c & x & "'!R8C3:R8C154,1,MATCH(RC3,'" & c & x &
"'!R15C3:R15C154,0))"
FindID = Evaluate(s)
End Function

--
Regards,
Tom Ogilvy


"pm" wrote in message ...
hello,

i try to write a function corresponding to index and match
functions used in excel spreadsheets..

i have sth like this:

Function FindID(c As Variant, x As Integer)

'c - file (ie. [file1.xls]
'x - arkusz (ie. 4

FindID = "=INDEX('" & c & x & "'!R8C3:R8C154,1,MATCH(RC3,'" & c & x &
"'!R15C3:R15C154,0))"

End Function

is it possible to build forumula in such a way?

best

Tom Ogilvy wrote:

Function FindID(c As Variant, x As Integer)

'c - file (ie. [file1.xls]
'x - arkusz (ie. 4

s = "=INDEX('" & c & x & "'!R8C3:R8C154,1,MATCH(RC3,'" & c & x & "'!R15C3:R15C154,0))"
FindID = Evaluate(s)
End Function

it doesn't work :/

I had to change x to String so I could pass it a sheet name, but once I did
that and put my workbook in R1C1 mode and entered data in the appropriate
places, it worked for me

=findid("[aa_test.xls]","Sheet1")

--
Regards,
Tom Ogilvy


"pm" wrote in message ...
Tom Ogilvy wrote:

Function FindID(c As Variant, x As Integer)

'c - file (ie. [file1.xls]
'x - arkusz (ie. 4

s = "=INDEX('" & c & x & "'!R8C3:R8C154,1,MATCH(RC3,'" & c & x &
"'!R15C3:R15C154,0))"
FindID = Evaluate(s)
End Function

it doesn't work :/

Tom Ogilvy wrote:

I had to change x to String so I could pass it a sheet name, but once I did
that and put my workbook in R1C1 mode and entered data in the appropriate
places, it worked for me

=findid("[aa_test.xls]","Sheet1")

now, function looks like this:

Function FindID(c As Variant, x As String)
'c - file
'x - sheet

s = "=INDEX('" & c & x & "'!R8C3:R8C154,1,MATCH(RC3,'" & c & x & "'!R15C3:R15C154,0))"
FindID = Evaluate(s)

End Function

and formula returns #ARG!

arguments c and x are results of formulas in other cells

i use this function in this way:

=findid(C5;B3)

value in C5 (c) is [names_en.xls]
value in B3 (x) is 4

Unless your sheet is named 4, then there is no way that will work. If you
want to refer to the 4th sheet in the tab order, then your function would
require more work:

Function FindID(c As Variant, x1 As Integer)

'c - file (ie. [file1.xls]
'x - arkusz (ie. 4

x = Worksheets(x1).Name

s = "=INDEX('" & c & x & "'!R8C3:R8C154,1,MATCH(RC3,'" & c & x &
"'!R15C3:R15C154,0))"

FindID = Evaluate(s)
End Function



--
Regards,
Tom Ogilvy


"pm" wrote in message ...
Tom Ogilvy wrote:

I had to change x to String so I could pass it a sheet name, but once I
did
that and put my workbook in R1C1 mode and entered data in the
appropriate
places, it worked for me

=findid("[aa_test.xls]","Sheet1")

now, function looks like this:

Function FindID(c As Variant, x As String)
'c - file
'x - sheet

s = "=INDEX('" & c & x & "'!R8C3:R8C154,1,MATCH(RC3,'" & c & x &
"'!R15C3:R15C154,0))"
FindID = Evaluate(s)

End Function

and formula returns #ARG!

arguments c and x are results of formulas in other cells

i use this function in this way:

=findid(C5;B3)

value in C5 (c) is [names_en.xls]
value in B3 (x) is 4