Databasing in Excel...i'm in way over my head :(

  • Thread starter Thread starter abxy
  • Start date Start date
A

abxy

If i knew Access, it'd be a great help, but unfortunately I don't, so
here's my problem:

I'm creating a Product Return Form for the Inventory department where I
work. Now, the thing is, there are 2 feilds, one for the system ID
number of the product, and the other for the description(name) of the
product. I'm trying to make it so that all you need to do is type in
the item ID and the corresponding product description will appear. When
I started, I used an IF function, basically saying if that cell has
this product ID in it, put the description in this cell. It worked.
Then I went to make it so that if the cell has a different Item ID in
it, it would display the description for the corresponding
product...but i'm stumped, how do I do that? ...also, it's not just 3
or 4 products, it's about 200 at least. How can I make this work?
Here's what I have:

=IF(A17="HNF0310221J","THINSULATE GLOVE..."," ")

How do I make it so that I can have more than one function working in
that cell, so that I can put in more item id's and corresponding
product names?
 
One way:

Assuming your Product Return Form is in Sheet1. Let's put the following in
Sheet2 beginning in cell A1:

1 Glove
2 Hat
3 Coat
4 Scarf
5 Boots

Then in Sheet 1, put the following formula in cell B1:

=VLOOKUP(A1,Sheet2!A2:B6,2)

In Sheet1, A1, enter the product ID. The corresponding product should
appear in cell B1.

Is this what you're looking for?

HTH
Mike
 
abxy said:
*If i knew Access, it'd be a great help, but unfortunately I don't,
so here's my problem:

I'm creating a Product Return Form for the Inventory department where
I work. Now, the thing is, there are 2 feilds, one for the system ID
number of the product, and the other for the description(name) of the
product. I'm trying to make it so that all you need to do is type in
the item ID and the corresponding product description will appear.
When I started, I used an IF function, basically saying if that cell
has this product ID in it, put the description in this cell. It
worked. Then I went to make it so that if the cell has a different
Item ID in it, it would display the description for the corresponding
product...but i'm stumped, how do I do that? ...also, it's not just 3
or 4 products, it's about 200 at least. How can I make this work?
Here's what I have:

=IF(A17="HNF0310221J","THINSULATE GLOVE..."," ")

How do I make it so that I can have more than one function
working in that cell, so that I can put in more item id's and
corresponding product names? *
Click to expand...

Create a table with 2 columns. In one column put the syatem ID number
and in the other put its description. Let's say this range is in c1 to
d100. You can then look up the description using
=vlookup(a17,c1:d100,2). See the vlookup help for more details.
 
...
...
Here's what I have:

=IF(A17="HNF0310221J","THINSULATE GLOVE..."," ")

How do I make it so that I can have more than one function working in
that cell, so that I can put in more item id's and corresponding
product names?
Click to expand...

Don't do this. It won't work for more than 8 items anyway. Create a table of
codes and descriptions, and use VLOOKUP.
 
Thanks a whole lot for all of your help guys, i'm gonna give a go a
this method, and something tells me it's gonna work.

I'll post my results as soon as i try i
 
This works!, This is what I'm looking for!. Thanks a lot guys.

One itsy bitsy thing though...

VLOOKUP is making it so that if I halfway type a product ID or mistyp
one, it returns the product that's the closest fit to what I hav
typed. That is EXTREMELY DANGEROUS seeing as how I work with stock an
inventory...I can't have the wrong products show up. I need VLOOKUP t
look for _exactly_ what i type in the product ID column; n
aproximations. If I mistype something, or halfway type it, I want t
see a cell error so that I can know i mistyped it. I do I do that?

I know it has something to do with the range_lookup, messing with tha
isn't giving me the results I need
 
...
...
VLOOKUP is making it so that if I halfway type a product ID or mistype
one, it returns the product that's the closest fit to what I have
typed. That is EXTREMELY DANGEROUS seeing as how I work with stock and
inventory...I can't have the wrong products show up. I need VLOOKUP to
look for _exactly_ what i type in the product ID column; no
aproximations. If I mistype something, or halfway type it, I want to
see a cell error so that I can know i mistyped it. I do I do that?
Click to expand...
...

Read online help for VLOOKUP. It has an optional 4th argument that enforces
exact (net of case sensitivity) matches.
 
Hi

Sure, that's the fourth optional argument. Set it to False to require exact matches.

=VLOOKUP(D1,A1:B3,2,FALSE)

From Help:

Syntax
VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
(snip)
Range_lookup is a logical value that specifies whether you want VLOOKUP to find an exact
match or an approximate match. If TRUE or omitted, an approximate match is returned. In
other words, if an exact match is not found, the next largest value that is less than
lookup_value is returned. If FALSE, VLOOKUP will find an exact match. If one is not found,
the error value #N/A is returned.
 
ok, see, that's that thing though, when I change the argument to false
I get #VALUE error now
 
ok, see, that's that thing though, when I change the argument to false,
I get #VALUE error now.
Click to expand...

What's the *EXACT* formula you're using? Paste it into a reply in PLAIN TEXT.
 
Back
Top