database help

[Guest]

I have 2 worksheets. One worksheet has a databse of: manufacturers - product
code - description - price.

The other worksheet is a cost sheet with multiple rows for the input of
product descriptions, model numbers and prices.

eg.
Item - Description - Qty - Product Code - Manufacturer - Price

Is there a way I could type model numbers in a cell and have the
description, manufacturer and price for that code automatically insert into a
cell on the same row?

Please note I know very little about macros or MS Access.

I would appreciate any help forthcoming.

Regards,

Jonny

 

[JulieD]

Hi Jonny

this can be done via the use of VLOOKUP
assume that your "database of manufacturers" is on sheet 2 with
..........A.........................B........................C.....................D
1.....Product Code......Description..........Price............Manufacturer

and your "other worksheet" is on sheet 1 with
...........A...................B.................C..................D...................E........................F
1.....Item...........Description......Qty...............Product
Code...Manufacturer......Code

assuming you want to enter the details in column D and have B, E, F
automatically fill
in D2 use the following formula

=VLOOKUP(D2,Sheet2!$A$2:$D$1000,2,0)
this says, have a look at the value in D2, find it in column A of sheet2 and
return the related information from the 2nd column (meaning of the 2) of the
table where there is an exact match (meaning of the 0)

likewise for E2 the formula would be
=VLOOKUP(D2,Sheet2!$A$2:$D$1000,4,0)

note, however, if D2 is blank you'll get a #NA error - one way to deal with
this is to embed the VLOOKUP in an IF statement, such as

=IF(ISNA(ISBLANK(D1)),"",VLOOKUP(D1,Sheet2!$A$2:$D$1000,2,0))

have a look at Help on VLOOKUP for a more details

Cheers
JulieD

 

[Guest]

Thanks JulieD that sounds like it is what I need but whenever I try anything
you said it brings up formulae error.

The exact setup is like so:

Database (worksheet)
.....A...........B.............C.............D
Supplier - Code - Description - Price

note: codes can be a mixture of letters and numbers (dont know if this will
matter?)

Cost Sheet (worksheet)
...A............B............C.......D..........F............G
Item - Description - Qty - Code - Supplier - Price

note: column E is not relevent (its only 2 pixels wide for some reason?) but
doesnt matter.

Thanks very much for the help

 

[JulieD]

Hi Jonny

if you're entering or choosing CODE in column D of the Cost Sheet and want
to populate supplied / description and price you'll need to change (if
possible) the structure of the database so CODE is in column A - if it's not
possible to change we'll have to go for an INDEX/MATCH solution rather than
a VLOOKUP solution.

However assuming you can change it and you have done so then the formula in
column B of the Cost Sheet would be
=VLOOKUP(D2,Database!$A$2:$D$1000,3,0)
- to test, type this -you'll get #NA but don't worry, then copy & paste a
value from the Code column on the Database sheet into cell D2 on the Cost
Sheet ... the associated description should be filled in.

Let us know how you go

Cheers
JulieD

 

[Guest]

Thank you very much Julie you have helped me very very much and I am forever
in your debt you are fantastic.

Jonny

PS. Thanks

 

[JulieD]

you're welcome <vbg> and thanks for the feedback

Thank you very much Julie you have helped me very very much and I am
forever
in your debt you are fantastic.

Jonny

PS. Thanks