|
Need to display price in $ or £ dependant on supplier
Hi,
I hope someone can point me in the right direction ! My workbook has a number of sheets as below. On my product list sheet I have a list of suppliers, some UK and some USA based, I have used a data validation list to allow the salesmen to select a product and display the cost =VLOOKUP(A20,partslist,2,FALSE) On the sales sheet I have two columns, A: USA list price B:UK list price I need to put the right cost in the correct column. Thanks for any help you can give. Jared. -- New excel user, please pass ! |
Need to display price in $ or £ dependant on supplier
How do you know if a supplier is USA or UK? That information probably needs
to be in your 'partslist' table. If your 'partslist' table was laid out something like: Part # UKorUSA Price abc-1 UK 2.99 def-2x USA 1.4 Then your VLOOKUP() could be like this: for the USA price column: =IF(VLOOKUP(A20,partslist,2,FALSE)="USA",VLOOKUP(A 20,partslist,3,FALSE),0) and the one for the UK price column: =IF(VLOOKUP(A20,partslist,2,FALSE)="UK",VLOOKUP(A2 0,partslist,3,FALSE),0) "Jared" wrote: Hi, I hope someone can point me in the right direction ! My workbook has a number of sheets as below. On my product list sheet I have a list of suppliers, some UK and some USA based, I have used a data validation list to allow the salesmen to select a product and display the cost =VLOOKUP(A20,partslist,2,FALSE) On the sales sheet I have two columns, A: USA list price B:UK list price I need to put the right cost in the correct column. Thanks for any help you can give. Jared. -- New excel user, please pass ! |
Need to display price in $ or £ dependant on supplier
Hi Sorry !
Yes the parts list sheet contains columns :location of supplier, cost of item ($ or £ dependent on nationality), part no. and description. I have been able to use vlookup to display the relevant part & description in the sales sheet, but this has two columns; A:"$ cost" and B:"£ cost" my problem is that the sales sheet was designed by the sales team and they need to retain the two seperate currency columns but only display the cost in the relevant one. Thanks Jared. -- New excel user, please pass ! "JLatham" wrote: How do you know if a supplier is USA or UK? That information probably needs to be in your 'partslist' table. If your 'partslist' table was laid out something like: Part # UKorUSA Price abc-1 UK 2.99 def-2x USA 1.4 Then your VLOOKUP() could be like this: for the USA price column: =IF(VLOOKUP(A20,partslist,2,FALSE)="USA",VLOOKUP(A 20,partslist,3,FALSE),0) and the one for the UK price column: =IF(VLOOKUP(A20,partslist,2,FALSE)="UK",VLOOKUP(A2 0,partslist,3,FALSE),0) "Jared" wrote: Hi, I hope someone can point me in the right direction ! My workbook has a number of sheets as below. On my product list sheet I have a list of suppliers, some UK and some USA based, I have used a data validation list to allow the salesmen to select a product and display the cost =VLOOKUP(A20,partslist,2,FALSE) On the sales sheet I have two columns, A: USA list price B:UK list price I need to put the right cost in the correct column. Thanks for any help you can give. Jared. -- New excel user, please pass ! |
Need to display price in $ or £ dependant on supplier
I would follow J Latham's advice but slightly modify the formulas to
=IF(VLOOKUP(A20,partslist,2,FALSE)="USA",VLOOKUP(A 20,partslist,3,FALSE),"") =IF(VLOOKUP(A20,partslist,2,FALSE)="UK",VLOOKUP(A2 0,partslist,3,FALSE),"") Note I have replaced the 0 be a pair of double quotes with nothing between If you are having trouble, send me a sample file - get my email from my website -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Jared" wrote in message ... Hi Sorry ! Yes the parts list sheet contains columns :location of supplier, cost of item ($ or £ dependent on nationality), part no. and description. I have been able to use vlookup to display the relevant part & description in the sales sheet, but this has two columns; A:"$ cost" and B:"£ cost" my problem is that the sales sheet was designed by the sales team and they need to retain the two seperate currency columns but only display the cost in the relevant one. Thanks Jared. -- New excel user, please pass ! "JLatham" wrote: How do you know if a supplier is USA or UK? That information probably needs to be in your 'partslist' table. If your 'partslist' table was laid out something like: Part # UKorUSA Price abc-1 UK 2.99 def-2x USA 1.4 Then your VLOOKUP() could be like this: for the USA price column: =IF(VLOOKUP(A20,partslist,2,FALSE)="USA",VLOOKUP(A 20,partslist,3,FALSE),0) and the one for the UK price column: =IF(VLOOKUP(A20,partslist,2,FALSE)="UK",VLOOKUP(A2 0,partslist,3,FALSE),0) "Jared" wrote: Hi, I hope someone can point me in the right direction ! My workbook has a number of sheets as below. On my product list sheet I have a list of suppliers, some UK and some USA based, I have used a data validation list to allow the salesmen to select a product and display the cost =VLOOKUP(A20,partslist,2,FALSE) On the sales sheet I have two columns, A: USA list price B:UK list price I need to put the right cost in the correct column. Thanks for any help you can give. Jared. -- New excel user, please pass ! |
Need to display price in $ or £ dependant on supplier
Hi Gent's
Many thanks for your Help - I will try the suggestions. Again Thanks. Jared. -- New excel user, please pass ! "Bernard Liengme" wrote: I would follow J Latham's advice but slightly modify the formulas to =IF(VLOOKUP(A20,partslist,2,FALSE)="USA",VLOOKUP(A 20,partslist,3,FALSE),"") =IF(VLOOKUP(A20,partslist,2,FALSE)="UK",VLOOKUP(A2 0,partslist,3,FALSE),"") Note I have replaced the 0 be a pair of double quotes with nothing between If you are having trouble, send me a sample file - get my email from my website -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Jared" wrote in message ... Hi Sorry ! Yes the parts list sheet contains columns :location of supplier, cost of item ($ or £ dependent on nationality), part no. and description. I have been able to use vlookup to display the relevant part & description in the sales sheet, but this has two columns; A:"$ cost" and B:"£ cost" my problem is that the sales sheet was designed by the sales team and they need to retain the two seperate currency columns but only display the cost in the relevant one. Thanks Jared. -- New excel user, please pass ! "JLatham" wrote: How do you know if a supplier is USA or UK? That information probably needs to be in your 'partslist' table. If your 'partslist' table was laid out something like: Part # UKorUSA Price abc-1 UK 2.99 def-2x USA 1.4 Then your VLOOKUP() could be like this: for the USA price column: =IF(VLOOKUP(A20,partslist,2,FALSE)="USA",VLOOKUP(A 20,partslist,3,FALSE),0) and the one for the UK price column: =IF(VLOOKUP(A20,partslist,2,FALSE)="UK",VLOOKUP(A2 0,partslist,3,FALSE),0) "Jared" wrote: Hi, I hope someone can point me in the right direction ! My workbook has a number of sheets as below. On my product list sheet I have a list of suppliers, some UK and some USA based, I have used a data validation list to allow the salesmen to select a product and display the cost =VLOOKUP(A20,partslist,2,FALSE) On the sales sheet I have two columns, A: USA list price B:UK list price I need to put the right cost in the correct column. Thanks for any help you can give. Jared. -- New excel user, please pass ! |
Need to display price in $ or £ dependant on supplier
They should work for you, one for the USA price column, one for the UK price
column. Naturally change the "USA" and "UK" in the formulas to however you actually have the countries marked in the table (along with the proper column references in it). I chose to display a zero for the "not right country" results, but as Bernard said, you may prefer to have nothing displayed in the cell at all. "Jared" wrote: Hi Gent's Many thanks for your Help - I will try the suggestions. Again Thanks. Jared. -- New excel user, please pass ! "Bernard Liengme" wrote: I would follow J Latham's advice but slightly modify the formulas to =IF(VLOOKUP(A20,partslist,2,FALSE)="USA",VLOOKUP(A 20,partslist,3,FALSE),"") =IF(VLOOKUP(A20,partslist,2,FALSE)="UK",VLOOKUP(A2 0,partslist,3,FALSE),"") Note I have replaced the 0 be a pair of double quotes with nothing between If you are having trouble, send me a sample file - get my email from my website -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Jared" wrote in message ... Hi Sorry ! Yes the parts list sheet contains columns :location of supplier, cost of item ($ or £ dependent on nationality), part no. and description. I have been able to use vlookup to display the relevant part & description in the sales sheet, but this has two columns; A:"$ cost" and B:"£ cost" my problem is that the sales sheet was designed by the sales team and they need to retain the two seperate currency columns but only display the cost in the relevant one. Thanks Jared. -- New excel user, please pass ! "JLatham" wrote: How do you know if a supplier is USA or UK? That information probably needs to be in your 'partslist' table. If your 'partslist' table was laid out something like: Part # UKorUSA Price abc-1 UK 2.99 def-2x USA 1.4 Then your VLOOKUP() could be like this: for the USA price column: =IF(VLOOKUP(A20,partslist,2,FALSE)="USA",VLOOKUP(A 20,partslist,3,FALSE),0) and the one for the UK price column: =IF(VLOOKUP(A20,partslist,2,FALSE)="UK",VLOOKUP(A2 0,partslist,3,FALSE),0) "Jared" wrote: Hi, I hope someone can point me in the right direction ! My workbook has a number of sheets as below. On my product list sheet I have a list of suppliers, some UK and some USA based, I have used a data validation list to allow the salesmen to select a product and display the cost =VLOOKUP(A20,partslist,2,FALSE) On the sales sheet I have two columns, A: USA list price B:UK list price I need to put the right cost in the correct column. Thanks for any help you can give. Jared. -- New excel user, please pass ! |
Need to display price in $ or £ dependant on supplier
Thank you both - each formula works fantasticaly.
Jared -- New excel user, please pass ! "JLatham" wrote: They should work for you, one for the USA price column, one for the UK price column. Naturally change the "USA" and "UK" in the formulas to however you actually have the countries marked in the table (along with the proper column references in it). I chose to display a zero for the "not right country" results, but as Bernard said, you may prefer to have nothing displayed in the cell at all. "Jared" wrote: Hi Gent's Many thanks for your Help - I will try the suggestions. Again Thanks. Jared. -- New excel user, please pass ! "Bernard Liengme" wrote: I would follow J Latham's advice but slightly modify the formulas to =IF(VLOOKUP(A20,partslist,2,FALSE)="USA",VLOOKUP(A 20,partslist,3,FALSE),"") =IF(VLOOKUP(A20,partslist,2,FALSE)="UK",VLOOKUP(A2 0,partslist,3,FALSE),"") Note I have replaced the 0 be a pair of double quotes with nothing between If you are having trouble, send me a sample file - get my email from my website -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Jared" wrote in message ... Hi Sorry ! Yes the parts list sheet contains columns :location of supplier, cost of item ($ or £ dependent on nationality), part no. and description. I have been able to use vlookup to display the relevant part & description in the sales sheet, but this has two columns; A:"$ cost" and B:"£ cost" my problem is that the sales sheet was designed by the sales team and they need to retain the two seperate currency columns but only display the cost in the relevant one. Thanks Jared. -- New excel user, please pass ! "JLatham" wrote: How do you know if a supplier is USA or UK? That information probably needs to be in your 'partslist' table. If your 'partslist' table was laid out something like: Part # UKorUSA Price abc-1 UK 2.99 def-2x USA 1.4 Then your VLOOKUP() could be like this: for the USA price column: =IF(VLOOKUP(A20,partslist,2,FALSE)="USA",VLOOKUP(A 20,partslist,3,FALSE),0) and the one for the UK price column: =IF(VLOOKUP(A20,partslist,2,FALSE)="UK",VLOOKUP(A2 0,partslist,3,FALSE),0) "Jared" wrote: Hi, I hope someone can point me in the right direction ! My workbook has a number of sheets as below. On my product list sheet I have a list of suppliers, some UK and some USA based, I have used a data validation list to allow the salesmen to select a product and display the cost =VLOOKUP(A20,partslist,2,FALSE) On the sales sheet I have two columns, A: USA list price B:UK list price I need to put the right cost in the correct column. Thanks for any help you can give. Jared. -- New excel user, please pass ! |
| All times are GMT +1. The time now is 02:46 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com