I have succesfully created a list using Data, Validation option.

This puts the name of the product in the cell C6, but I am trying to
find a way of automatically bringing the cost price of the product into
D6 and the retail price into E6.

I have tried using a string of if statements but after around 10
multiples, it comes up as an error all the time.

Any ideas please

Thanks


--
Markyboy
------------------------------------------------------------------------
Markyboy's Profile: http://www.excelforum.com/member.php...o&userid=29493
View this thread: http://www.excelforum.com/showthread...hreadid=491929

I think the more common approach to your situation is:

On a separate sheet, create a listing of Products, Cost, RetailPrice

Example:
Product/Cost/Price table on Sheet2, A1:C500

Select that range, then
Type LU_ProdPrices in the Name Box (just above the Col_A heading)
Press [Enter] (That creates a named range)

Then, on your input sheet:
A1: Item
B1: Cost
C1: Price

A2: (your product code)
B2: =VLOOKUP(A2,LU_ProdPrices,2,0)
C2: =VLOOKUP(A2,LU_ProdPrices,3,0)

Copy the formulas in B2 and C2 down as far as needed.

Note: if you don't want to see errors associated with blank Items in
Col_A, use these formulas:
B2:
=IF(ISERROR(VLOOKUP(A2,LU_ProdPrices,2,0)),"",VLOO KUP(A2,LU_ProdPrices,2,0))
C2:
=IF(ISERROR(VLOOKUP(A2,LU_ProdPrices,3,0)),"",VLOO KUP(A2,LU_ProdPrices,3,0))

Does that give you something to work with?

Regards,
Ron


--
Ron Coderre
------------------------------------------------------------------------
Ron Coderre's Profile: http://www.excelforum.com/member.php...o&userid=21419
View this thread: http://www.excelforum.com/showthread...hreadid=491929

Thanks Ron

Will give it a go,

Thanks


--
Markyboy
------------------------------------------------------------------------
Markyboy's Profile: http://www.excelforum.com/member.php...o&userid=29493
View this thread: http://www.excelforum.com/showthread...hreadid=491929

Have a lookup table on another sheet in three columns

A product name

B cost price

C retail price

On Sheet1 in D6 enter =VLOOKUP(C6,Sheet2!$A$1:$C$100,2,FALSE)

In E6 enter =VLOOKUP(C6,Sheet2!$A$1:$C$100,3,FALSE)

The A1:C100 is example only. Your range may differ.


Gord Dibben Excel MVP

On Thu, 8 Dec 2005 12:54:12 -0600, Markyboy
wrote:


I have succesfully created a list using Data, Validation option.

This puts the name of the product in the cell C6, but I am trying to
find a way of automatically bringing the cost price of the product into
D6 and the retail price into E6.

I have tried using a string of if statements but after around 10
multiples, it comes up as an error all the time.

Any ideas please

Thanks