VLOOKUP results displays ##### in some. Why?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Blue-eyed girl - 17 Oct 2006 19:29 GMT

Person A enters new or additional comments on Workbook 1. I have a VLOOKUP on
Workbook 2 that retrieves those comments but somethimes it displays just ###
signs. I have formatted the text to wrap. I have replaced the formula on book
2 with a new VLOOKUP formula in hopes of correcting the error but it does
not work. Is it possible that there is some symbol in the book 1 comments
that is causing this to happen?

Reply to this Message

Teethless mama - 17 Oct 2006 20:40 GMT

Increase your column width

> Person A enters new or additional comments on Workbook 1. I have a VLOOKUP on
> Workbook 2 that retrieves those comments but somethimes it displays just ###
> signs. I have formatted the text to wrap. I have replaced the formula on book
> 2 with a new VLOOKUP formula in hopes of correcting the error but it does
> not work. Is it possible that there is some symbol in the book 1 comments
> that is causing this to happen?

Reply to this Message

Blue-eyed girl - 17 Oct 2006 21:00 GMT

I tried increasing my column width - now I have a hundred # signs!

Reply to this Message

Kevin Vaughn - 17 Oct 2006 22:06 GMT

Is it possible that the vlookup is returning a negative number and your range
is formatted as date (or time?)

Signature

Kevin Vaughn

> I tried increasing my column width - now I have a hundred # signs!
>
[quoted text clipped - 4 lines]
> > not work. Is it possible that there is some symbol in the book 1 comments
> > that is causing this to happen?

Reply to this Message

Blue-eyed girl - 17 Oct 2006 23:28 GMT

The VLOOKUP pulls Comments from the other workbook. Here is what it is trying
to pull:

10/6/06-Called requesting blah. This is "final" blah. It's tied to X01111
which hasn't blah. Working to blah. 10/16/06-P.O. Change blah. Working
blah X01111 issue blah.

The same type of comments are in this column and 98% of them are pulling
across without all those #### signs!

I tried removing the quote marks from book 1 but that did not correct the
issue.

> Is it possible that the vlookup is returning a negative number and your range
> is formatted as date (or time?)
[quoted text clipped - 7 lines]
> > > not work. Is it possible that there is some symbol in the book 1 comments
> > > that is causing this to happen?

Reply to this Message

Gord Dibben - 17 Oct 2006 23:53 GMT

I see that the example string contains 175 characters including spaces.

I'm not sure what "blah" rerpresents.

Longer text string perhaps?

If so.............One other potential cause for #############

Excel has a problem with cells that contain >255 but <1024 characters.

Fornat the cell to General and your ############## should go away.

Gord Dibben MS Excel MVP

>The VLOOKUP pulls Comments from the other workbook. Here is what it is trying
>to pull:
[quoted text clipped - 20 lines]
>> > > not work. Is it possible that there is some symbol in the book 1 comments
>> > > that is causing this to happen?

Gord Dibben MS Excel MVP

Reply to this Message

Blue-eyed girl - 18 Oct 2006 00:11 GMT

Formatting the cells to General took care of the problem! Thanks a zillion.

BTW: The "blah" was confidential text.

> I see that the example string contains 175 characters including spaces.
>
[quoted text clipped - 36 lines]
>
> Gord Dibben MS Excel MVP

Reply to this Message

Gord Dibben - 18 Oct 2006 04:25 GMT

Thanks for the feedback.

Gord

>Formatting the cells to General took care of the problem! Thanks a zillion.
>
[quoted text clipped - 40 lines]
>>
>> Gord Dibben MS Excel MVP

Gord Dibben MS Excel MVP

Reply to this Message