VLookup Problem

[bwall]

I'm using a VLOOKUP function to pull up a clientName from a 2n
worksheet and apply it to the 1st worksheet (tab named Book2). My 2n
worksheet (tab named ClientDB3) contains a client DB with the ClientI
and ClientName. The VLOOKUP fx is used by the 1st worksheet which is
client report basically.

I have a column on the report page (1st worksheet) that has a list in
dropdown box that fills with the clientID from the 2nd worksheet as the
are added to the Client DB (2nd worksheet). Both the ClientID and th
ClientName columns are defined to grow as entries are added in the 2n
worksheet. I have a list dropdwon data validation defined for bot
columns in the 1st sheet, could this be the problem???? Should
eliminate the dropdown for the Name and only have the =VLOOKUP f
used.

In book2 1st worksheet when I pick a ClientID from the dropdown box,
would like the ClientName to automatically fill in adj column.

My fomula is:

=VLOOKUP(A8,ClientName,1,FALSE)

which is placed in the column adj to A8 (the ClientID column)


It returns a #NA value

 

[BenjieLop]

I'm using a VLOOKUP function to pull up a clientName from a 2n
worksheet and apply it to the 1st worksheet (tab named Book2). My 2n
worksheet (tab named ClientDB3) contains a client DB with the ClientI
and ClientName. The VLOOKUP fx is used by the 1st worksheet which is
client report basically.

I have a column on the report page (1st worksheet) that has a list in
dropdown box that fills with the clientID from the 2nd worksheet as the
are added to the Client DB (2nd worksheet). Both the ClientID and th
ClientName columns are defined to grow as entries are added in the 2n
worksheet. I have a list dropdwon data validation defined for bot
columns in the 1st sheet, could this be the problem???? Should
eliminate the dropdown for the Name and only have the =VLOOKUP f
used.

In book2 1st worksheet when I pick a ClientID from the dropdown box,
would like the ClientName to automatically fill in adj column.

My fomula is:

=VLOOKUP(A8,ClientName,1,FALSE)

which is placed in the column adj to A8 (the ClientID column)


It returns a #NA value.

ASSUME that in your worksheet 2 (ClientDB3),

1. ClientID is A1:A100 and
2. ClientName is B1:B100

So, your formula in worksheet 1 is

=VLOOKUP(A8,CLIENTDB3!$A$1:$B$100,2,0

Hope this will help you.

Regards

 

[bwall]

I have the columns for the ID and Name in ClientDB3 sert for a dynami
range on both columns. The define name is ClientID for ID an
ClientName for name.

I tried setting VLOOKUP to:

=VLOOKUP(A8,ClientDB3!ClientName,1,FALSE) o
Book2!ClientName

but that didn't work

I thought if you had a range name you could subsitute that for th
cells or rows.


I need to have a dynamic range because entries will constantly be adde
to the client DB

 

[swatsp0p]

As long as all sheets are in the same workbook, you don't need t
identify a range with the sheet name.

My guess is the values in the dropdown box do NOT match those in A8
etc.

Try changing your formula by removing the FALSE and making it TRUE.
What do you get now? If you get either the first or last name in th
list, you can be confident that either the spellings or spaces are no
the same in both places.

Just a thought

 

[Dave Peterson]

But if the OP did have sheet level names on each sheet, then that worksheet name
would be required.

 

[Dave Peterson]

If you select the clientDB3 worksheet and then hit Edit|Goto and type:
ClientName
do you select the range you expect?

And if you eyeball that column 1 of that range and pick out the cell that you
think matches and use:

=a8=clientdb3!x99
(x99 is the cell that holds that match)

Do you get true or false back.

Along with Swatsp0p's warning about extra spaces in the cell, excel will see a
difference between numeric values and text values (123 <> '123).

If you change the format (to general or to Text), then reenter the value, does
it work ok?