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]

OK, with excel 2003, it will notify you of an irregularity in a field. I just figured this out .

The reason it is formatting the same as the text is because it is considered a 'numer as text'. The problem is, since the data is coming through ODBC from a database, it is Microsofts fault that it is coming in as text. The ironic thing is, Microsofts fix is not a fix at all, but a band-aid. They say to select the fields that are like this, and basically multiply them by one, which will make them an actual number. Whatever..

Anyway, this is far from a solution for me since my data is organized alphanumerically, and where a 7 digit part number that is numeric may be grouped with other part numbers that are wholly numeric, it may be preceeded and followed by a part number that has a Z at the end.. In other words, no way to select just the fields that contain numbers as text, and leave out the rest. Why the lookup cant figure this out is beyond me. In my opinion, a serious bug, but who am I to say..

The solution, as I was able to come up with anyway, is to make the numbers in the small sheets into text as well. This is the same solution as when you type in a number in a general field and it becomes a date by the date number system. I guess Microsoft decided that we arent smart enough to type in a number without some assistance in deciding what it is for us if we didnt specify, so it will take a number that starts with 1-12 and convert it to a date for us. The workaround being putting a single quote in front of it. So instead of going through the 400-600 items that are pure numerical in my master sheet and making them a number, which would reset itself every time I reset the data, I am making the numerical part numbers into text on the individual sheets.

If there is a way to do this without the quote, please someone enlighten me, as it would help me a lot. You would think that perhaps designating a field as text would make a number into a 'number as text', but alas Microsofts logic is far beyond that.. so far in fact that I cannot possibly understand what the heck they are thinking. I mean, why can I multiply that text by one to make it a number if there was not some code in there that recognized that it is in fact a number.

Once again, any help would be appreciated, but I am out of the red for the moment, as I have a workaround that is, while cumbersome, at least feasible.

Thanks

Dave