HELP!! numeric data wont link..

[dkersten]

OK, let me explain this as quickly as I can. I wrote a long detailed explanation, but when I went to post it it errored out and I lost the whole thing. So I will keep it short and to the point.

I have an excel sheet of data that I pulled through ODBC from some proprietary database. There is 2 columns, one is a part number and one is a price. I also have about 500 individual excel files that are used to drop pricing into product catalog made with pagemaker. After a lot of help from posts on this forum, I was able to hack out a formula that does a vlookup for a part number where the array is the sheet with this "master" data that I created from our database. If the part number does not exist in the list, it looks to the next sheet on the file, which is a manual list of those products we do not stock, where I can manually price those products, and at catalog revision time, print out and re-price. This way, most of my prices are able to be updated through ODBC, then updated in the individual excel files, then updated in Pagemaker, all with a few keystrokes instead of manually adjusting 10000 parts.

The formula works great, but I ran into a problem about 40 hours into the process of applying this formula and linking all the files correctly.

The problem is when I have a purely numberic part number. It will not find it although it exists in the master sheet. After a lot of frustration, I discovered that if I select the part number in the master sheet, and highlight it in the formula box, and then just click off that field or press enter or anything, the number moves from being left justified like the rest of the data to being right justified. After that, it links properly. I tested this on a dozen numbers with the same result. I cannot figure out what is changing, and most importantly, how to apply those changes to the entire list of 10000 parts so that I dont have to spend 20 hours selecting the fields, highlighting the numbers and clicking the next field, rinse, repeat, etc... And since I am doing all this work so I can refresh the ODBC data and easily update my catalog, the idea of having to do this for all 600ish part numbers every time I refresh is upsetting to say the least.

Can anyone explain to me what is happening, and what I can do to fix it? Why does alphanumeric work fine, but numeric not work? why does the number move from the left to the right in the field once I do this, even though I am not copying and pasting or re-typing the number, or for that matter changing it in any way..?

here is the formula I am using:
=IF(ISERROR(VLOOKUP(A3,'[1 Master Price Sheet.xls]ICMAST PRICING'!$1:$9999,1,FALSE)),IF(ISERROR(VLOOKUP(A3,'[1 Master Price Sheet.xls]Non-Stock Pricing'!$A$2:$C$500,1,FALSE)),"Non Stock",VLOOKUP(A3,'[1 Master Price Sheet.xls]Non-Stock Pricing'!$A$2:$C$500,3,FALSE)),VLOOKUP(A3,'[1 Master Price Sheet.xls]ICMAST PRICING'!$A$2:$B$9999,2,FALSE))

All fields are formatted as general, but changing them to numbers is not helping.

Please help if you can.. I am now desperate.

Thanks

Dave

[dkersten]

mods, please delete. When I went to post, it timed out, so I reposted.. now I am frustrated AND embarassed. :P