vLookup Limitation of 255 text characters

[ddebruin]

Hello, all. An old one, but a good one. It has been documented, in prior threads, that there appears to be a limit of 255 text characters when doing a vLookup. I have two spreadsheets, let's say "A" and "B". I have a vLookup in "A" that is looking for information in "B". The vLookup works fine and returns all data fine until the 256 character in a text string, then it truncates.

Here is an interesting twist. This only happens if "B" is closed. If spreadsheet "B" is open, then there is no limit, in fact, the vLookup successfully returned a text string of 1,000+ characters. The second I close "B", though, "A" begins to show the truncation.

I have become quite good at vLookups over the past few weeks, but I did not understand the solution that some of you gave in prior posts.

In "A", I have an SPRF number in column A. In "B", I have an SPRF number in col G. What I am doing, using vLookup, is looking for the SPRF number in "B" and then displaying the contents (text) of column K from "B" in "A".

If "B" is open, all is well, "A" shows all text, even > 255. If "B" is closed, then "A" shows only through 255.

Thoughts?

 

[GlennUK]

... I did not understand the solution that some of you gave in prior posts ...

... and what solution would that be exactly?

 

[ddebruin]

In a variety of other posts (maybe even on other sites), people gave examples of using indexing and a more complex Lookup. I was hoping there was an easier answer staying with vLookups, the sheet I am referring to has about 50 vLookups in it and I would hate to introduce more complex formulas as it will need to be published out for a wide-variety of people to use. Thanks!

 

[JD_GFI]

A crude method would be to write a routine into either the workbook_open or worksheet_activate events to open the 2nd workbook.