HELP - Need help with a spreadsheet, probably easy to do

kopernikuz · May 2nd, 2002, 01:49 PM

Okay, I have a list of Item Numbers and their prices.

On another page I have a list of Sales Reps, and how many they sold of each item. For example:

This is quantity sold
John
Item1 2
Item2 3
Item3 5
Jane
Item1 3
Item3 2
Rick
Item2 3

I need to create a field after the Qty that will check the price based on the item column and multiply it times the Qty column.

Whats the best way to do this?

[ This Message was edited by: kopernikuz on 2002-05-02 12:50 ]

Anne Troy · May 2nd, 2002, 01:54 PM

Something like:

=if(c2>0,c2*vlookup(b2,Sheet2!$A$1:$B$20,2,0),"")

where:

b2 contains the item number
c2 contains your item quantity
Sheet2!A1:B20 contains your product number and price columns in 20 rows.

sen_edp · May 2nd, 2002, 02:13 PM

Hello There,

In my Humble opinion, i think that when a poster gets his answer , the least he can do is say "thanks, it works"

Andreas

Nice Work Dreamboat !

Anne Troy · May 3rd, 2002, 07:54 AM

Thanks!

sen_edp · May 3rd, 2002, 07:58 AM

You welcome

Andreas

May 2nd, 2002, 01:54 PM	#2
Anne Troy MrExcel MVP Join Date: Feb 2002 Location: Allentown, PA Posts: 2,510	Something like: =if(c2>0,c2*vlookup(b2,Sheet2!$A$1:$B$20,2,0),"") where: b2 contains the item number c2 contains your item quantity Sheet2!A1:B20 contains your product number and price columns in 20 rows. __________________ ~Anne Troy

May 3rd, 2002, 07:54 AM	#4
Anne Troy MrExcel MVP Join Date: Feb 2002 Location: Allentown, PA Posts: 2,510	Thanks! __________________ ~Anne Troy

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 2nd, 2002, 01:49 PM	#1
kopernikuz New Member Join Date: Mar 2002 Posts: 7	Okay, I have a list of Item Numbers and their prices. On another page I have a list of Sales Reps, and how many they sold of each item. For example: This is quantity sold John Item1 2 Item2 3 Item3 5 Jane Item1 3 Item3 2 Rick Item2 3 I need to create a field after the Qty that will check the price based on the item column and multiply it times the Qty column. Whats the best way to do this? [ This Message was edited by: kopernikuz on 2002-05-02 12:50 ]

May 2nd, 2002, 02:13 PM	#3
sen_edp Board Regular Join Date: Mar 2002 Location: Hellas Posts: 553	Hello There, In my Humble opinion, i think that when a poster gets his answer , the least he can do is say "thanks, it works" Andreas Nice Work Dreamboat !

May 3rd, 2002, 07:58 AM	#5
sen_edp Board Regular Join Date: Mar 2002 Location: Hellas Posts: 553	You welcome Andreas