You should do a search for dynamic named ranges and data validation.
There are some files I use at:
http://normay.741.com/

Look at PO_Master_12_26_07_S.xls and RFQ_Master_05_17_07.xls.
They both have dynamic named ranges and data validation drop down cells.
Just add some parts data, unprotect the sheets and look at the named ranges and
and some of the formulas that are to the left of the data validation drop down
cells.

Did the function call get copied from the actual macro?
if so "WroksheetFunction...." should actually be
"WorksheetFunction..."

Sorry. That was a typo.
The VBA code is pretty involved and I didn't know how much to
transfer. I work  on a separate laptop devoted to the client to keep
it aligned with the corporate installed image.

After posting this, I found the old post on using .Find instead. This
is getting me closer, but not quite there. I'll discuss my issue on
.Find as a reply to that suggestion by another post.

I'm not sure this addresses the same issue, but could you use the
Find instead?, as in: Set fc = Worksheets("Sheet2").Columns
("A").Find(what:=actVal)

In a VBA form, it might look something like this:

Sub findmatchandmove()

Dim actVal
'<----the variable of the active record to be found
Dim newVal
'<---the variable of the value that needs to be moved
Dim fc
'<---variable for the found cell that matches
Dim fndRrow
'<---variable for the row  that matches
actVal = ActiveCell.Value
'<--- actVal is located in the active cell
newVal = ActiveCell(Offset, 5).Value
'<--- location of newVal, in this example column  5
Set fc = Worksheets("Sheet2").Columns("A").Find(what:=actVal)
'<--finds it on the other sheet
fndRrow = fc.Row
'<--identifies the row it's on in the list
Sheets("Sheet2").Cells(fndRrow, 5).Value = newVal
'<---puts the value in column 5 of the newly found row
MsgBox "Item information is now on Sheet2 on line = " & fndRrow
End Sub

of course, you'd substitute which sheet  and which columns for each
item you were matching, as well as which column  on the same row  that
the item to be moved is in, and run a second line for the second
item to be moved. I wasn't clear in my other message, sorry.

It also occurred to me that you would need to handle the error, if
it didn't find the Identifying value(actVal) in the column  you're
looking for it in, as in:

On Error Resume Next
Set fc = Worksheets("Sheet2").Columns("A").Find(what:=actVal)
fndIRrow = fc.Row
If Err.Number = 91 Then 'it didn't find it
'<---so, go the next one, or whatever you need to do
else
Sheets("Sheet2").Cells(fndRrow, 5).Value = newVal
'<---put the value where you want it, etc.
End If

Yes, this gets me closer, but not yet there.

It is important to add
Dim fc As Range 'since fc is actually a cell in the target sheet.

Let me explain what I am trying to do.
Running down the rows of Sheet1 where column  A has the employee name
or a blank (vEmployee), if not blank then I switch focus to Sheet2 and
.Find against Column D where .value = vEmployee. When these match, I
want to pick up the .value in the matching  cell, copy it to the
original source cell and pick up the .value of (Offset, 1) for the
found cell and copy it to source cell (Offset, 26).

This corrects the employee name when there is a minor variance like a
middle initial not used, and inserts the manager's name. Next, I do a
second look up against the manager's list on Sheet3 to match  the
manager to get his/her email address.

Find definitely works, but I keep getting minor errors or I'm not
pointing to the right column or sheet. Any help on how to code this
will be appreciated. When I have the working code, I will post it.

I like using the "Match" function since it can be set to find an exact match.
I believe the "Find" function requires the LookUp list ot be sorted ascending to
work right & may not guarantee an exact match.
Not sure if I follow your flow of events, but give this a try (I havent tested
it fully):

Sub testLookUpName2()
vGetTotalRowsInData = Range("A1").CurrentRegion.Rows.Count
For vRowNumber = 2 To vGetTotalRowsInData
vNameToLookUp = Sheets("Sheet1").Range("A" & vRowNumber).Value
If Not (IsEmpty(vNameToLookUp)) Then

If IsError(vRowMatch = WorksheetFunction.Match(vNameToLookUp,
Sheets("Sheet2").Range("D:D"), 0)) Then
'skip: not found
Else
vRowMatch = WorksheetFunction.Match(vNameToLookUp,
Sheets("Sheet2").Range("D:D"), 0)
vActualEmployeeName = Sheets("Sheet2").Range("E" & vRowMatch).Value
Sheets("Sheet1").Range("A" & vRowNumber).Offset(0, 26).Value = vNewEmployeeName
End If

End If
Next vRowNumber

End Sub

I may have misunderstood
Harrison, but I thought he was wanting to look for names WITHIN the
cell . . . BECAUSE he might not get an exact match  of the whole cell
value, such as an employee who was entered without a middle initial,
versus his other list, which might include the middle initial in the
employee's name. I did think the rest of your code looked great,
so I'm going to save it to my code examples for future use myself,
heheh, thanks--but I haven't tested it yet either, so if you test it
out and adjust it, I'd be interested in the update.

Anyway, I'm looking forward to hearing from Harrison as to if
these examples work, or a combination of them. Anyone working with
this, please keep me informed (at least here in the group) if you
find other ways to do this, since it comes up in my work  on occasion.

Yeah,.... this was at 2:00AM for me! I could be missing the picture!
I read the original post but still was not exactly sure what was going on.
I think sleep would have been a better option!

Could you please copy out the actual code involved. One minor phrase of it
(including a typo) is not enough to go on - we need the whole statement and
probably any statements near it.

What error do you get?

OK everyone. I figured it out using the Find function. It turns out
to be plenty fast on a laptop.

I'm going to give you the full code since there is nothing proprietary
and it has been thoroughly tested. If you take my user case as a
guide, you'll be able to see what I did.

The code is initiated by a button on the "Instructions" Sheet. It can
only be activated after certain other conditions are satisfied that
have set up the two Sheets, "Employees" and "Managers", that are used
for the look up process. The "Employees" sheet  is generated from the
corporate database which is not always up to date, or may have had
ex-employees' names removed thereby causing an no match  condition.

The keys to making this work  is to be explicit in specifying the
Sheets().Cells(), Sheets().Columns(), and other cell references,
because the default of ActiveSheet, etc., sometimes gets confused.
Further, I use 'On Error' clauses to handle the not found condition.

FYI, my next step I am doing today. It will then take the "Travel
History" Sheet and distribute it by Department to the various Managers
by email.

OOps.... I made an error that I forgot to fix before sending this
out. Right now, the cell in Row(AC) is getting the match, or the
previous match  when not found. I'll fix it and post the correction,
only the correction.

If you want me to upload the code as a .txt for future use, please
tell me where to put it.