I am trying to create a function that uses the vlookup worksheet function to
look up a value from the previous sheet. eg. The sheet "Mar 2005" will lookup
the values in "Feb 2005" and then "Feb 2005 " will look at "Jan 2005", and so
on, the sheets are in order Jan 2005, Feb 2005,... . The function is only to
ask the user the value to look up and then it will look up the previous
corresponding table on previous sheet.

does something like this help:

Sub TestMe()
Dim Ans As String
Dim Response As String

Ans = InputBox("Enter some Text", "My Title", "Hello")
'look for Ans in col 1 of MyTable and return the value in col 4 of same
range
Response = Application.VLookup(Ans, MyTable, 4, False)
MsgBox Response
' more code
End Sub

"Ziv" wrote:

I am trying to create a function that uses the vlookup worksheet function to
look up a value from the previous sheet. eg. The sheet "Mar 2005" will lookup
the values in "Feb 2005" and then "Feb 2005 " will look at "Jan 2005", and so
on, the sheets are in order Jan 2005, Feb 2005,... . The function is only to
ask the user the value to look up and then it will look up the previous
corresponding table on previous sheet.

Assuming the value to lookup is in A1, the lookup range is A1:H100, and the
tab names have an embedded space then

=VLOOKUP(A1,INDIRECT("'"&TEXT(DATEVALUE("01
"&MID(CELL("Filename",A1),FIND("]",CELL("Filename",A1))+1,255))-1,"mmm
yyyy")&"'!A1:H100"),2,FALSE)

won't work on Jan of course

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Ziv" wrote in message
...
I am trying to create a function that uses the vlookup worksheet function
to
look up a value from the previous sheet. eg. The sheet "Mar 2005" will
lookup
the values in "Feb 2005" and then "Feb 2005 " will look at "Jan 2005", and
so
on, the sheets are in order Jan 2005, Feb 2005,... . The function is only
to
ask the user the value to look up and then it will look up the previous
corresponding table on previous sheet.