Automatic Product Search

  • Thread starter Thread starter Marvin van Dongen
  • Start date Start date
M

Marvin van Dongen

Hello guys,

I have a question about Excel. Here's what i want to do:

-1 table (Table A) with article numbers and articlenames
-1 table (Table B) with other data, and 1 column for article number and 1
for articlename.

What i want to do is place articles in table A and fill in the article
number and names. Then when i go to table B and typ in the articlecode, it
automaticly fills in the article name that goes with it from table A. Can
you guys tell me how to do that?

Thnx very much,

Marvin
 
Marvin

Data on Sheet1 Range A1:C1000, In B2 on sheet 2 type

=IF(ISNA(VLOOKUP(A2,Sheet1!$A$1:$C$1000,2,FALSE)),"",VLOOKUP(A2,Sheet1!$A$1:$C$1000,2,FALSE))

This will show nothing until you make an entry in A2 and then will show the
second column from Sheet1. To adapt for the third column, change the '2' to
'3'.

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
 
Marvin,

You're describing a join between your tables. Access does this in its
sleep.

You can do this with VLOOKUP in the table B. Without knowing your table
layouts, I can't give you an example.
 
Thnx for the help nick. Im currently struggling with the code, cause my
excel is in dutch. And yes of course......they change the language of the
code as well. Is there a way to implement it in VBA (wich IS in english)?

Marvin
 
Marvin

It can be done in VBA but that's real overkill and translator gave me this
for Lookup

raadpleging

So maybe

VRAADPLEGING?????

=VRAADDPLEGING(A2,Blad1!$A$1:$C$1000,2,VALS)

Probably miles away!

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
 
Thnx for the help, unfortunalty it doesnt work. What i did find out:

IF = ALS
VLOOKUP=VERT.ZOEKEN
False=ONWAAR

That leaves me with ISNA. Maybe if you can describe its function, i can
search for it.

Marvin

P.S. While helping me, you get a free lesson dutch ;-)
 
Marvin

It is in the 'information' group of functions. It returns true if the result
of the formula is Excel's error of #N/A! (Not Available), that is it can't
find a match.

The group consists of

ISNA
ISERROR
ISERR

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
 
Victory! It works a treat! Thnx very much for ur help. Ow and for ur
interest, the final function became:

=ALS(ISNB(VERT.ZOEKEN(A6;(Blad1!$A$1:$C$1000);2;ONWAAR));"";VERT.ZOEKEN(A6;B
lad1!$A$1:$C$1000;2;ONWAAR))

I also had to change , to ;

Anyway, it works! I'm gonna mess around with it to implement it into my
current sheet. If i ran into trouble, i'll let you know.

Thnx,

Marvin
 
Great

For information, you don't need the ( ) around the first (Blad1!........)

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
 
One little question: what i want a third column that he has to read from
table A and place into table B when i select an article number.

Marvin
 
Back
Top