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

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

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

"Jonny" wrote in message
...
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

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

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



"Jonny" wrote in message
...
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

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" wrote:

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



"Jonny" wrote in message
...
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

you're welcome <vbg and thanks for the feedback

"Jonny" wrote in message
...
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" wrote:

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



"Jonny" wrote in message
...
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