VLookup problem when a new row has been inserted

[Guest]

I use the function VLOOKUP($C$7,EmployeeData,2) in cell c8 in sheet1 to pull
the second column(EmployeeNumber) from the employees sheet(EmployeeName,
EmployeeNumber, DepartmentNumber) when the cell c7(EmployeeName) is selected
in the combox. Here "employeedata" is a namerange defined big enough to hold
the data in employees sheet.

In the begining, there are 23 rows in the Employees sheet and this formula
works, anytime in sheet1 the "EmployeeName" is selected in the combox c7 and
the corresponding EmployeeNumber is showing up in c8. But after inserting a
row(employee record) in the employees sheet, sort the sheet. The vlookup
function does not work for the row after 23rd. the cell in formula
VLOOKUP($C$7,EmployeeData,2) is just empty, even without a error code
returned.

This drove me crazy and do not understand why the function works for the
data in 1-23 rows but not for any new rows? The sheet is sorted already. Oh,
my neck is sore, any clues? I would appreciate.

 

[JulieD]

Hi

if you defined the range EmployeeData as containing 23 rows, then it won't
recognise any more. Check out the definition of employeedata in
insert/name/define.

If you want the employeedata range to expand when new data is added then
you'll need to use a dynamic range name - Debra Dalgleish has a good
explaination of how to do this on her website at
www.contextures.com/tiptech.html

Cheers
JulieD

 

[Bernd Plumhoff]

Are you really sure the data is sorted? If not, then try
VLOOKUP($C$7,EmployeeData,2,FALSE).

Are you really sure that nobody inserted a column? If not
then retrieve the search and the lookup range (S1:S999 and
L1:L999, maybe, if in same sheet) and try INDEX
(L1:L999,MATCH($C$7,S1:S999,FALSE)).

HTH,
Bernd