Validation List +

[Mike]

What I have is a validation list of products. What I would like to do
is choose a product from the list and as a result of that choice, has
the size and cost are automatically input into two separate cells.

Can anyone give me some insight on how to solve this problem?

Any help would be appreciated. Thanks...Mike

 

[J.E. McGimpsey]

What I have is a validation list of products. What I would like to do
is choose a product from the list and as a result of that choice, has
the size and cost are automatically input into two separate cells.

Can anyone give me some insight on how to solve this problem?

Any help would be appreciated. Thanks...Mike

One way:

On a separate sheet, say Sheet2, enter your table of products:

A B C
1 PRODUCT SIZE COST
2 prod1 small $1.00
3 prod2 med $10.00
4 prod3 large $100.00
....
50 prod50 immense $1000000

Assume that your validation cell is A1 on Sheet1. Then in Sheet1
enter:

B1: =IF(A1="","",VLOOKUP(A1,Sheet2!$A$1:$C$50,2,FALSE))
C1: =IF(A1="","",VLOOKUP(A1,Sheet2!$A$1:$C$50,3,FALSE))

To make this easier to maintain, I would name the Sheet2 table
"ProdTable" using

Insert/Name/Define
Name in worksheet: ProdTable
Refers to: =OFFSET(Sheet2!$A$2,,,COUNTA(Sheet2!$A:$A)-1,3)

so that you can add or remove products without changing your table.
Then your formulae would be:

B1: =IF(A1="","",VLOOKUP(A1,ProdTable,2,FALSE))
C1: =IF(A1="","",VLOOKUP(A1,ProdTable,3,FALSE))

And I would use a named range in Validation:

Insert/Name/Define
Name in worksheet: ProdList
Refers To: =OFFSET(ProdTable,,,,1)

to ensure that when products are added to or removed from ProdTable,
they're also added to or removed from the validation dropdown. Set
up validation like this:

Allow: list
Source: =ProdList

 

[anonymous]

I am just learning this Validation list stuff. I have created a validation
list to populate a certain cell (let's say A1. however what I want to do
next is use the result A1 to populate a result in B2. There are five
different options in the A1 validation list and based upon which item
they choose I want to populate a different result in B2. what I am doing
is working on a product pricing spreadsheet where based upon the Tier
level they pick in A1 it assigns a price in B2. how do I do this?

 

[J.E. McGimpsey]

I am just learning this Validation list stuff. I have created a validation
list to populate a certain cell (let's say A1. however what I want to do
next is use the result A1 to populate a result in B2. There are five
different options in the A1 validation list and based upon which item
they choose I want to populate a different result in B2. what I am doing
is working on a product pricing spreadsheet where based upon the Tier
level they pick in A1 it assigns a price in B2. how do I do this?

You can look at Debra Dalgleish's site for a tutorial on dependent
validation lists:

http://contextures.com/xlDataVal02.html