To get a price from price list while making a purchase order

Closed
Report
Posts
4
Registration date
Wednesday May 28, 2014
Status
Member
Last seen
May 29, 2014
-
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
-
Hi,I would like to know how to create a macro code I have two files one named Price list and other Purchase Order. Actually I wanted like this ,the price should come automatically from the price list when entered an Item in purchase order without opening the price list workbook. Can you please help me to solve this issue.

eg; In the price List

Name of the item Price
Soap 6.00
Tooth brush 10.00

In the Purchase order
Name of the item Price
Soap ?

So while typing the item in the purchase order the price should come automatically without opening the price list.
Thanking you in advance.

5 replies

Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
803
better do not have two workbooks or files. have two sheet.
in sheet1 type the data as follows

item price
soap 6
tooth brush 10


in sheet 2
row 1 header row
item price

in A2 you enter soap (spelling correct as in sheet1)
IN b2 USE THIS FORMULa

=VLOOKUP(A2,Sheet1!A1:B3,2,0)

you will get 6

you need not open sheet1
Posts
4
Registration date
Wednesday May 28, 2014
Status
Member
Last seen
May 29, 2014

But mine is two workbook i cant have one workbook for both. So on that case what can i do.And also if i have n number of items to be purchase in the order should i mention the formula in all the rows or how.
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
803
suppose book3 contains data (source data)
book4 is query file

the modified formula will be

=VLOOKUP(A2,[Book3]Sheet1!$A$1:$B$3,2,0)

change the name of the workbook(file) realistically
Posts
4
Registration date
Wednesday May 28, 2014
Status
Member
Last seen
May 29, 2014

Can you please explain how does the above formula work. And since i will be having some 10000 nos of items so how can I amend the above formula.
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
803
you need ot amend the formula much . suppose in book3.sheet1 there are one hundred row. then instead of $B$3 use $B$100 (modify to suit the data


the formula in B2 of second sheet

=VLOOKUP(A2,[Book3]Sheet1!$A$1:$B$100,2,0)
copy B2 down as long as data is there is data in column A
for doing this quick
take cursor to right bottom of B1 when cursor turns to bold positive sign(+)
click this positive sign formula in B1 will be copied down, A2 changing to A3, a4 etc.
experiment on this type of copying

what does this formula do
it takes A2 (suppose "soap")
and goes to book3!sheet1 and finds out where soap is there in column A
and copies the entry in corresponding 2nd column that is column B

if no soap found then formula will give error (#N/A)

see help under vlookup