Lots of syntax problems. Try something like (untested):

Function last_done_date() As Date

'returns last_done_date

last_done_date = WorksheetFunction.VLookup _
(ActiveCell.Offset(-1), Range("Menu"), 4, False)

End Function

--

Vasant


"Brian Rogge" <brian -at- minnplace dot com wrote in message
. ..
I am attempting to define a function that looks up a value on another
worksheet and returns that value to the active cell. Sample data is below
and the spreadsheet is attached.

Basically I want enter the meals on sheet 1 by looking up the value of
sheet1.week-day on sheet 2 and then return the entree for that value.
Currently I am using =VLOOKUP(G4,Menu,4,0). 'Menu' is the range on sheet
2.
The problem is there are 20,0000 rows on sheet 1 and the workbook is
getting
large - actually there is another part of the workbook that is causing the
size problem but fixing this will help me fix the other. My attempt at
the
user-defined function is listed below. It returns #Ref right now. Any
guidance would be appreciated.

R/

Brian

Sheet 1 Sheet 2
Meal Week-Day Week-Day Week # Day Entrée
Week 1-Monday Week 1-Monday Week 1 Monday Hamburgers
Week 2-Friday Week 1-Tuesday Week 1 Tuesday Hot Dogs
Week 2-Tuesday Week 1-Wednesday Week 1 Wednesday Pizza
Week 1-Thursday Week 1-Thursday Week 1 Thursday Chicken
Week 2-Thursday Week 1-Friday Week 1 Friday Stew
Week 2-Monday Week 2-Monday Week 2 Monday Pork Chops
Week 2-Wednesday Week 2-Tuesday Week 2 Tuesday Roast
Week 2-Wednesday Week 2 Wednesday Hamburgers
Week 2-Thursday Week 2 Thursday Hot Dogs
Week 2-Friday Week 2 Friday Chicken



Function last_done_date()
'returns last_done_date
VLookup = Application.VLookup
Offset = Application.Offset
last_done = VLookup(Offset(ActiveCell, 0, 1, 1, 1), Menu, 4, 0)

End Function