Help needed with cell category, ie Number/Text etc

[Guest]

I am using a lookup formula in Excel 2003 to display a product description
when I enter a product code.

I am having a problem with choosing what category to assign to the cells.
The product code can start with a number (some start with a 0) or a letter.
If I assign the cells to be a number, when I enter a code that starts with 0
such as 0109 it changes it to 109. We need it to show the 0 in front.

If I assign the cells to be text, the product codes such as 0109 work and it
shows the right product description but when I use a product code that
doesn't start with 0 such as 2208 it doesn't show the product description
from the lookup table.

Any help would be v.much appreciated.

 

[Bob Phillips]

Set it up in the same way that you have setup the key column in the lookup
table.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

Thanks for your quick reply. Both columns are set up exactly the same.

Is it to do with the formula??

=(IF(ISNA(VLOOKUP(A1395,LOOKUP!$A$1:$B$201,2,FALSE)),"",VLOOKUP(A1395,LOOKUP!$A$1:$B$201,2,FALSE)))

 

[Bob Phillips]

LOOKUP is not a good name for a worksheet, as it is a function name. Try
another name.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

I have changed the worksheet name to "Product Table" but the problem is still
there.

 

[Guest]

It sounds as though your problem lies in the way the product codes are
entered in the lookup table. My surmise is that some codes are in the table
as text entries while others are numeric entries.

Quick fix -
1) format the column of product codes as text
2) if there aren't too many codes, highlight the range of codes and re-enter
each one by pressing the F2 key followed by the Enter key until all have been
re-entered.
3) if there are too many to do manually, right click on the Product Table
sheet's tab, select View Code, and paste this code in

Sub ReEntry()
Dim cc As Range
For Each cc In Selection
cc.Value = cc.Text
Next
End Sub

then select the range of product codes and run that code agaisnt it.

Good luck
Duke

 

[Bob Phillips]

Format the lookup table key column and the data entries to General.

--

HTH

RP
(remove nothere from the email address if mailing direct)