formatting issue

[inspin]

okay im mainly a database guy so im finding some excel quirks..here is an annoying one for me..

I export from one dbase into excel so that some people in my dept. can use them for whatever purposes they need. (its 1 workbook with 5 sheets)

now the problem is a little complicated to explain. But I use a few "VLOOKUP"'s to fill in data for them in the spreadsheets..this works great if the vlookups occur on fields where the data is in there from the export from the database..if it is not it does not work because of a formatting issue. heres an example:

I use vlookup up the the policy# field it works fine if the policy# field is filled in from the dbase export. If it is not you cannot manually enter the policy number you have to copy it from somewhere else ont he page that has exported the policy number and then paste it into the field your trying to add!

Ive checked the formatting..they are all consistent so I have no idea why it matters..but it does..

if anyone understood what I was talking about and can give me any clues please let me know..this one just doesnt make any sense..


and thanks to all who have answered by previous 2 posts i appreciate it

-Ins

 

[Seti]

Is it possible that the policy number is text in Access and if you manually input it in Excel it is numeric? If so, VLOOKUP will not work. You need to make sure they are the same datatype/format type.

 

[inspin]

thats the thing when they get moved form the access to excel they are type "general" and when they add policy numbers it is still type "general"

however the vlookup will not work...has me stumped still.

thanks though

-ins

 

[IML]

You can't just see how the cell if formatted - both text and "real" number can reside in a cell format as general. Test both the item you are looking up, and the supposed match with this formula
=isnumber(cell ref). If both are true, or both are false, your fine, if they differ, it will not recogize as a match. Do the policy number have a consistent # of characters?

On 2002-11-07 14:09, inspin wrote:
thats the thing when they get moved form the access to excel they are type "general" and when they add policy numbers it is still type "general"

however the vlookup will not work...has me stumped still.

thanks though

-ins

 

[inspin]

yes they both have tested as false and the policy # is a set amount of characters and do not grow or shrink from that amount.

thanks again.

-Ins

 

[IML]

Shoot, I thought that would be it. Another candiate is length. Are the values the same when you use the =len() function on the same two cells?