formatting issue

inspin

New Member
Joined
Oct 29, 2002
Messages
24
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
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Seti

Well-known Member
Joined
May 19, 2002
Messages
2,916
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

New Member
Joined
Oct 29, 2002
Messages
24
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

MrExcel MVP
Joined
Feb 15, 2002
Messages
1,743
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

New Member
Joined
Oct 29, 2002
Messages
24
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

MrExcel MVP
Joined
Feb 15, 2002
Messages
1,743
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?
 

Forum statistics

Threads
1,144,743
Messages
5,726,022
Members
422,653
Latest member
mntsiki

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top