Lists help

[Markyboy]

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

[Ron Coderre]

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)),"",VLOOKUP(A2,LU_ProdPrices,2,0))
C2: =IF(ISERROR(VLOOKUP(A2,LU_ProdPrices,3,0)),"",VLOOKUP(A2,LU_ProdPrices,3,0))

Does that give you something to work with?

Regards,
Ron

[Markyboy]

Thanks Ron

Will give it a go,

Thanks

[Gord Dibben]

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
<[email protected]> 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