G
Guest
I am working in Excel 2003 SP1, and am using a VLookup function that has
worked beautifully until last friday. My function references a named range
on a different sheet within the same workbook, but I am now getting error
messages for some lookups, and not others.
The named range has a calculated text field at the in the first column,
listing the last four digits (all numbers) of an ID number. The real ID
number, located in a later column of the same named range, is alphanumeric
and longer, but the last four digits are unique.
The second column in the named range is a calculated text field where Rank,
First Name and Last Name are joined from later parts of the named ranged,
using "" & "", etc., to spell the name right.
Other fields have phone numbers, supervisor names, region, etc.
In the destination sheet, I have a data validation pull-down list comprised
of the first column of the named range - to avoid typing errors. Then, in
the adjacent columns to the pull down list, I call corresponding values from
other columns in the named range into later columns of the destination sheet.
The code looks like "=IF(N460="","",VLOOKUP(N460,IDNumbers,2,TRUE))", but of
course the "N460" and the "2" change depending on the location in the
destination sheet.
Last Friday, while the lookup function was still working, I noticed that one
of our old investigators was taken off the list in the named range - so I
added him back to the list. I left the calculated fields alone, but added
the correct information to the non-calculated fields. Then I sorted the list
in the named range. Our re-inserted investigator, #0009 was properly sorted
to the top of the list.
My list has just about 80 names in it, with ID "numbers" ranging from 0009
to 9807, and four blank lines at the end. But now something happens near the
end. The lookup for investigator #8746 and all the others before it work
fine. But the lookup for the next investigator, #8770, and all the others
after it, no longer work. Curiously, the lookup for the calculated name
field returns nothing, the lookup for a roman numeral field (Region) returns
#N/A, and the lookup for all other fields returns 0 - This last bit is
probably just a curiousity, but may be a clue to someone smarter than I.
My first thought was that when I added a person back onto the list, all
later names were messed up, but it was last on the list when I added it, and
first on the list after I sorted it - it did not hit the spot where the
problem appears.
Does anyone have an idea what I did that messed this up, and how I can fix
it now? Thanks.
worked beautifully until last friday. My function references a named range
on a different sheet within the same workbook, but I am now getting error
messages for some lookups, and not others.
The named range has a calculated text field at the in the first column,
listing the last four digits (all numbers) of an ID number. The real ID
number, located in a later column of the same named range, is alphanumeric
and longer, but the last four digits are unique.
The second column in the named range is a calculated text field where Rank,
First Name and Last Name are joined from later parts of the named ranged,
using "" & "", etc., to spell the name right.
Other fields have phone numbers, supervisor names, region, etc.
In the destination sheet, I have a data validation pull-down list comprised
of the first column of the named range - to avoid typing errors. Then, in
the adjacent columns to the pull down list, I call corresponding values from
other columns in the named range into later columns of the destination sheet.
The code looks like "=IF(N460="","",VLOOKUP(N460,IDNumbers,2,TRUE))", but of
course the "N460" and the "2" change depending on the location in the
destination sheet.
Last Friday, while the lookup function was still working, I noticed that one
of our old investigators was taken off the list in the named range - so I
added him back to the list. I left the calculated fields alone, but added
the correct information to the non-calculated fields. Then I sorted the list
in the named range. Our re-inserted investigator, #0009 was properly sorted
to the top of the list.
My list has just about 80 names in it, with ID "numbers" ranging from 0009
to 9807, and four blank lines at the end. But now something happens near the
end. The lookup for investigator #8746 and all the others before it work
fine. But the lookup for the next investigator, #8770, and all the others
after it, no longer work. Curiously, the lookup for the calculated name
field returns nothing, the lookup for a roman numeral field (Region) returns
#N/A, and the lookup for all other fields returns 0 - This last bit is
probably just a curiousity, but may be a clue to someone smarter than I.
My first thought was that when I added a person back onto the list, all
later names were messed up, but it was last on the list when I added it, and
first on the list after I sorted it - it did not hit the spot where the
problem appears.
Does anyone have an idea what I did that messed this up, and how I can fix
it now? Thanks.