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

Question

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.

venkat1926 · Answer

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

swarna79 · Answer

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 · Answer

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 · Answer

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.

venkat1926 · Answer

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

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

5 responses

Similar discussions

Subscribe To Our Newsletter!