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 !

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 !

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 !

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 !

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 !

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 !