To get a price from price list while making a purchase order
Closed
swarna79
Posts
3
Registration date
Wednesday May 28, 2014
Status
Member
Last seen
May 29, 2014
-
May 28, 2014 at 08:18 AM
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - May 30, 2014 at 12:31 AM
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - May 30, 2014 at 12:31 AM
Related:
- To get a price from price list while making a purchase order
- My contacts list names - Guide
- Steam purchase history - Guide
- Hard disk price - Hard Drive & SSD Forum
- Samsung keypad mobile price - Phones, PDA & GPS Forum
- Samsung Mobile GT-E1200Y unlock code - Phones, PDA & GPS Forum
5 responses
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
May 29, 2014 at 06:22 AM
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
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
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
Posts
3
Registration date
Wednesday May 28, 2014
Status
Member
Last seen
May 29, 2014
May 29, 2014 at 06:26 AM
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
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
May 29, 2014 at 07:12 AM
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
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
3
Registration date
Wednesday May 28, 2014
Status
Member
Last seen
May 29, 2014
May 29, 2014 at 09:33 AM
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?
Ask a question
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
May 30, 2014 at 12:31 AM
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
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
