VLOOKUP Problem
 

I have a spreadsheet that pulls data from another sheet. I enter an account
number in my main sheet and it goes to a table of accounts and pulls the
General Ledger description for that account. The sheet that I am pulling
data from is created by Cryatal Reports and exported to an Excel file (7
advanced). I know that Excel sometimes has problems with imported data and I
figured out by highlighting all the data and useing TEXT TO COLUMNS feature
and using delimited and turn everything off, this fixes that problem, so I
did that to the data file. When I enter the account number in my
spreadsheet, sometimes it will pull it correctly and other times I get that
N/A error. I will go to my data file and do that TEXT TO COLUMNS deal again
and when I come back to my input sheet, the G/L description magically
appears. I will save the data file again. Why doesn't this work
consistantly? I have never had this problem in the past.

I'm guessing that each time the CR program runs, it creates a fresh copy of your
data. But when it does that, it writes the numeric data as text.

When you do the data|text to columns, you're actually converting those text
numbers to number numbers.

Maybe you could talk to the person who wrote the CR dump to generate that field
as numeric.

Or maybe you could modify your =vlookup() formula to do the conversion for you:
=VLOOKUP(A2&"",[book2.xls]Sheet1!$A:$D,2,FALSE)
or even make sure it matches the same format:
=VLOOKUP(text(A2,"000000"),[book2.xls]Sheet1!$A:$D,2,FALSE)

depends on what that CR data looks like.




MParham wrote:

I have a spreadsheet that pulls data from another sheet. I enter an account
number in my main sheet and it goes to a table of accounts and pulls the
General Ledger description for that account. The sheet that I am pulling
data from is created by Cryatal Reports and exported to an Excel file (7
advanced). I know that Excel sometimes has problems with imported data and I
figured out by highlighting all the data and useing TEXT TO COLUMNS feature
and using delimited and turn everything off, this fixes that problem, so I
did that to the data file. When I enter the account number in my
spreadsheet, sometimes it will pull it correctly and other times I get that
N/A error. I will go to my data file and do that TEXT TO COLUMNS deal again
and when I come back to my input sheet, the G/L description magically
appears. I will save the data file again. Why doesn't this work
consistantly? I have never had this problem in the past.

--

Dave Peterson