Hidden Formatting?

[bigbavarian]

Hello everyone,

I am using the VLookup function with two seperate sheets. The first sheet is what I have created. The second sheet is one that was given to me.

I know how to build the format as I have done it numerous times in the past. This one only works if I copy the data into notepad, then copy it back into excel. Then it will work. I have looked at the formatting and I cannot see any differences.

Can anyone tell me a way to get it to work without the notepad part?

Thanks,

Stephen

[shg]

Build what format? Copy what into Notepad?

[bigbavarian]

I am sorry. I have been so frustrated that I was a little scattered and wasn't clear and failed to add that information.

I know how to build the formula not format.

I have a column of numbers for ids that appear in both tables that look like this..

100214521
100000984
200014521
Etc.

[shg]

Sorry, I still have no clue: What format?

[bigbavarian]

It is in the General format. I tried to change the format to number and text as well. Still no luck.

[shg]

Can you post a workbook and explain in context?

[bigbavarian]

I have attached the example. You will see on page 1 I have placed the VLookup formula in column B.

There are two funky things that are happening with the workbook. Try to see if you can create a VLookup on page 1 that pulls information from page 2. I cannot get it to work unless I copy column b of page 2 and paste into notepad. The copy the data from the notepad and past back into tab b of page 2.

Really strange.

Another think I noticed is that you cannot take the fill background off of page 1.

Go ahead and try.

Stephen

[bigbavarian]

I wanted to revive this discussion. It seams like it lost steam over the weekend. Does anyopne have any ideas why this is occurring?

[ChemistB]

I cannot access your workbook for some reason. (something about htm files missing). It does sound like Excel is seeing some numbers as text (and others as numbers). Try this for each list (assuming your lookup number is in A1); in a blank cell =ISNUMBER(A1). If some come up true and others FALSE, that's your problem.

ChemistB

[bigbavarian]

Thank you for the reply. I tried the function and it was not a number so I changed the format to number but still no luck.

Then I stumbled upon something very strange. I did a find and replace and replaced all the zeros with zeros. Any numbers with the zeros then worked.

This is making me pull my hair out.

[ChemistB]

One of the irritating things about Excel is that when you change the cell from text to number, Excel doesn't recognize the fact until a calculation occurs within the cell. I guess replacing all the zeros with zeros qualifies. Typically the trick is to use Data>text to columns or multiply all cells by 1 (using Paste Special>multiply). Glad you got it working.

ChemistB

[bigbavarian]

I tried your suggestion and I like it. I also told a couple others I work with and it was well received.

Thank you so much for the suggestion.

Stephen