VLookup Problem

[bwall]

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

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

In book2 1st worksheet when I pick a ClientID from the dropdown box, I 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 2nd worksheet and apply it to the 1st worksheet (tab named Book2). My 2nd worksheet (tab named ClientDB3) contains a client DB with the ClientID and ClientName. The VLOOKUP fx is used by the 1st worksheet which is a client report basically.

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

In book2 1st worksheet when I pick a ClientID from the dropdown box, I 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 dynamic range on both columns. The define name is ClientID for ID and ClientName for name.

I tried setting VLOOKUP to:

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

but that didn't work

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


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

[swatsp0p]

As long as all sheets are in the same workbook, you don't need to 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 the list, you can be confident that either the spellings or spaces are not 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.



swatsp0p wrote:
>
> As long as all sheets are in the same workbook, you don't need to
> 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 the
> list, you can be confident that either the spellings or spaces are not
> the same in both places.
>
> Just a thought.
>
> --
> swatsp0p
>
> ------------------------------------------------------------------------
> swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
> View this thread: http://www.excelforum.com/showthread...hreadid=466357

--

Dave Peterson

[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?

bwall wrote:
>
> I have the columns for the ID and Name in ClientDB3 sert for a dynamic
> range on both columns. The define name is ClientID for ID and
> ClientName for name.
>
> I tried setting VLOOKUP to:
>
> =VLOOKUP(A8,ClientDB3!ClientName,1,FALSE) or
> Book2!ClientName
>
> but that didn't work
>
> I thought if you had a range name you could subsitute that for the
> cells or rows.
>
> I need to have a dynamic range because entries will constantly be added
> to the client DB.
>
> --
> bwall
> ------------------------------------------------------------------------
> bwall's Profile: http://www.excelforum.com/member.php...o&userid=26997
> View this thread: http://www.excelforum.com/showthread...hreadid=466357

--

Dave Peterson