Thank you for being a valued part of the CNET community. As of December 1, 2020, the forums are in read-only format. In early 2021, CNET Forums will no longer be available. We are grateful for the participation and advice you have provided to one another over the years.

Thanks,

CNET Support

General discussion

large data v-look up?

Jul 31, 2006 11:43PM PDT

Hello, I've currently got one excel sheet with a large amount of private data and I have been using another sheet with a v-look up to pick out the parts I need.

I now have cells with more than 255 characters and v-look up doesn't seem to pick up anything beyond that. (I've searched the web and although i've found lots of people with similar problems, can't find a helpful way round)

Can anyone suggest a work around?

I'm willing to move to word. But I can't use VBA.

Or can anyone give me a formula that checks for more than 255 characters and a second formula to pick up the remains?

I'm desperate.

Discussion is locked

- Collapse -
What about Access?
Aug 1, 2006 12:15AM PDT

You mention Word but what about transitioning to Access? It sounds like you are using Excel as a database now.

- Collapse -
You are right.
Aug 1, 2006 12:21AM PDT

I was hoping to avoid access as I'm not as skilled with it. If it's the only way I guess I'll just have to add another string to my bow.

Also although I'm setting it up and I'll be around to help with problems, I'm not actualyl the person using it, I fear the people using it would be put off by access (I am! and I consider myself relatively computer literate)

- Collapse -
Me Too
Aug 1, 2006 12:23AM PDT

I have been put off by Access, but once you get into it, it isn't that bad. One thing I would suggest if you are setting it up for another user is to create a switchboard and have the user access forms from there. This keeps them from getting lost.

- Collapse -
Re: vlookup
Aug 1, 2006 12:38AM PDT

Vlookup generally looks up something that translates as a unique key in a database table (say: a part number, or a country code, or a customer ID).
It would be interesting to know the application that needs a 256-bytes unique key (and the people able to type such without errors).

Kees

- Collapse -
Yes, I guess excel isn't the right prog to use
Aug 1, 2006 1:08AM PDT

It started as a relatively simple thing, but basically a description section has been added that is causing all the problems...

If there is a way to make the look up pick up taxt from another source I'd be happy with that to?

- Collapse -
Okay..let's back-up...
Aug 1, 2006 1:31AM PDT

I am a little confused based on Keys question and your response.

What exactly is the user inputting and what exactly is getting returned?

Are they inputting >255 characters and expecting some piece of data to get returned or are they entering a piece of data and having a large text box returned?

If it is the first, I would wonder why someone was having to type in that much data to get a response. If it is the later, it should work. I just tested it in Office 2003 and it returend much more than 255 characters. The only limitation I see is that the cell will only display the first 1,024 characters in the spreadsheet and has a limit of 32,767 that it will display in the formula bar.

- Collapse -
I know it's a bit late but...
Oct 10, 2006 4:28AM PDT

Yeah, both.

Basically the user is incharge of the first sheet, into which they put HR information (to do with my companies vacancies, the job description being the long bit). And then in another sheet that I've already formatted with V-look-ups, they just put the job number, so they can only send specific jobs to certain agencies (all the accounts one's to the accounts employemnt agency, that kind of thing) and all the information pops up without them having to copy stuff.

BTW I fixed the problem, by telling them not to put Job descriptions of more than 256 chars! But it is an annoyance.

- Collapse -
Some users don't like the idea ...
Aug 1, 2006 3:42AM PDT

that they have to pay for a total redesign of an application, just because they have some 'minor' new requirements. Still, it's necessary sometimes, if the initial design (or the initial tool) can't be stretched enough to cover those requirements.

Kees

- Collapse -
It's interesting you say that.
Oct 10, 2006 4:36AM PDT

This is actually a stop gap measure, we have a proper peice of software (actually I hate it, but that's another story) and it does have a section for exactly what the problem related to.

But we are waiting for the go ahead from head office before we can use it (it's been about 8 months so far, my guess is sometime after the olympics come to London!)

So it's not really a money problem (I work in a very very profitable sector sector at the moment... even more than IT!) it's a waiting for managment (which in this case happens to be a couple of thousand miles away) to get their finger out of their ****.

- Collapse -
Text vs General
Aug 2, 2006 3:45AM PDT

Just a quick note on the VLookup of large values. I am not sure if it was related, but a co-worker was just having problems displaying text in a cell. They were insistant that there was a 256 character limit when I had just looked it up and the cell will hold like 3000+ and display the first 1024. The 256 applies to cells formatted as text. Cells formatted as General do not have this problem.

Does changing the cell format allow Vlookup to lookup more characters?

- Collapse -
Nope sorry.
Aug 2, 2006 6:25PM PDT

Just tryed it, all the cells that i'm having a problem with are formated as general.