VLookup Error in Part of a Named Range
 

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.

VLookup Error in Part of a Named Range
 

Hi!

Hard to say what's causing your problem. If you want to send me your file
I'll take a look. If you're interested in doing that just let me know how to
contact you.

Biff

"Dallas64" wrote in message
...
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.

VLookup Error in Part of a Named Range
 

I would love to send you a copy to look at, but I will have to strip some
confidential information off first. How is the best way to get a copy to you.

"Biff" wrote:

Hi!

Hard to say what's causing your problem. If you want to send me your file
I'll take a look. If you're interested in doing that just let me know how to
contact you.

Biff

"Dallas64" wrote in message
...
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.

VLookup Error in Part of a Named Range
 

Send to:

xl can help at comcast period net

Remove "can" and change the obvious.

Biff

"Dallas64" wrote in message
...
I would love to send you a copy to look at, but I will have to strip some
confidential information off first. How is the best way to get a copy to
you.

"Biff" wrote:

Hi!

Hard to say what's causing your problem. If you want to send me your file
I'll take a look. If you're interested in doing that just let me know how
to
contact you.

Biff

"Dallas64" wrote in message
...
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.

VLookup Error in Part of a Named Range
 

Second attempt to post this reply. I would be happy to send you my file,
after I've stripped some unrelated confidential information from it. How
should I get it to you?

"Biff" wrote:

Hi!

Hard to say what's causing your problem. If you want to send me your file
I'll take a look. If you're interested in doing that just let me know how to
contact you.

Biff

"Dallas64" wrote in message
...
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.

VLookup Error in Part of a Named Range
 

OK....

Just change the last argument in the lookup function to FALSE or 0 for
short:

=IF(N460="","",VLOOKUP(N460,IDNumbers,2,0))

Biff

"Biff" wrote in message
...
Send to:

xl can help at comcast period net

Remove "can" and change the obvious.

Biff

"Dallas64" wrote in message
...
I would love to send you a copy to look at, but I will have to strip some
confidential information off first. How is the best way to get a copy to
you.

"Biff" wrote:

Hi!

Hard to say what's causing your problem. If you want to send me your
file
I'll take a look. If you're interested in doing that just let me know
how to
contact you.

Biff

"Dallas64" wrote in message
...
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.

VLookup Error in Part of a Named Range
 

Perfect! Thanks!!!

"Biff" wrote:

OK....

Just change the last argument in the lookup function to FALSE or 0 for
short:

=IF(N460="","",VLOOKUP(N460,IDNumbers,2,0))

Biff