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.

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" <[email protected]> wrote in message
news:[email protected]...
> 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.
>

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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.
> >
>
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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.
> > >
> >
> >
> >

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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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.
> > >
> >
> >
> >

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.
>

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

--

HTH

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


"Martc" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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.
> > > >
> > >
> > >
> > >