Need to display price in $ or £ dependant on supplier

[Jared]

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.

 

[JLatham]

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(A20,partslist,3,FALSE),0)
and the one for the UK price column:
=IF(VLOOKUP(A20,partslist,2,FALSE)="UK",VLOOKUP(A20,partslist,3,FALSE),0)

 

[Jared]

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.

 

[Bernard Liengme]

I would follow J Latham's advice but slightly modify the formulas to
=IF(VLOOKUP(A20,partslist,2,FALSE)="USA",VLOOKUP(A20,partslist,3,FALSE),"")
=IF(VLOOKUP(A20,partslist,2,FALSE)="UK",VLOOKUP(A20,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

 

[Jared]

Hi Gent's

Many thanks for your Help - I will try the suggestions.

Again Thanks.

Jared.

 

[JLatham]

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]

Thank you both - each formula works fantasticaly.

Jared