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

Closed
swarna79
Posts
4
Registration date
Wednesday May 28, 2014
Status
Member
Last seen
May 29, 2014
- May 28, 2014 at 08:18 AM
venkat1926
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
- May 30, 2014 at 12:31 AM
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.
Related:

## 5 replies

venkat1926
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
810
May 29, 2014 at 06:22 AM
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
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
swarna79
Posts
4
Registration date
Wednesday May 28, 2014
Status
Member
Last seen
May 29, 2014

May 29, 2014 at 06:26 AM
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.
venkat1926
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
810
May 29, 2014 at 07:12 AM
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
swarna79
Posts
4
Registration date
Wednesday May 28, 2014
Status
Member
Last seen
May 29, 2014

May 29, 2014 at 09:33 AM
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.

Didn't find the answer you are looking for?

venkat1926
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
810
May 30, 2014 at 12:31 AM
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