Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Help needed with cell category, ie Number/Text etc
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. |
#2
|
|||
|
|||
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) "Martc" wrote in message ... 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. |
#3
|
|||
|
|||
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" wrote: 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) "Martc" wrote in message ... 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. |
#4
|
|||
|
|||
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) "Martc" wrote in message ... 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" wrote: 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) "Martc" wrote in message ... 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. |
#5
|
|||
|
|||
I have changed the worksheet name to "Product Table" but the problem is still
there. "Martc" wrote: 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" wrote: 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) "Martc" wrote in message ... 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. |
#6
|
|||
|
|||
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 "Martc" wrote: 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. |
#7
|
|||
|
|||
Format the lookup table key column and the data entries to General.
-- HTH RP (remove nothere from the email address if mailing direct) "Martc" wrote in message ... I have changed the worksheet name to "Product Table" but the problem is still there. "Martc" wrote: 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" wrote: 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) "Martc" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to create a variable column in cell reference | Excel Worksheet Functions | |||
How can I get a cell locked if the condition is false? | Excel Worksheet Functions | |||
Can a Formula in Cell X modify Cell Y? | Excel Discussion (Misc queries) | |||
inserting data from a row to a cell, when the row number is specified by a formula in a cell | New Users to Excel | |||
VLookup resulting in a blank cell... | Excel Worksheet Functions |