Drop Down List Reference Problem

[Guest]

I am using this formula in hopes of having a drop down list appear in column
J if "Yes" is selected from a drop down list in column I.

=IF(I2="Yes",Paint,"").

"Paint" is the name of my list and it has about 50 different types and
colors of paint. When I select "Yes", only the first item of the list appears
and there is no arrow to make a different selection.

I also want the J column to be blank if the next user selects "No" and
default to "No" when cell A2 changes or when the document is first opened.

Any help would be greatly apreciated!

 

[Bob Phillips]

Wally,

You need to use an allow type of list for that formula, not Custom.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Debra Dalgleish]

To create a dropdown list, you can use Data Validation in column J.
There are instructions in Excel's Help, and here:

http://www.contextures.com/xlDataVal01.html

You'll also need to define a range to be used for the dropdown, when Yes
is not entered in cell A2. For example, select a cell, and enter the
formula: =" "
Name that cell, e.g. NotYes

Then, select cell J2, and choose Data>Validation
For Allow, select List
In the Source box, type: =IF(I2="Yes",Paint,NotYes)

To clear column J, or to set defaults if another cell is changed, you
could use programming.

 

[Guest]

Debra,
Such an easy way around the problem...If you know what you're doing. I was
close, but you got the cigar. Thank You Very Much. It works great!

 

[Guest]

Bob,
I followed Debra's response and it worked, but Thank You for your response.

 

[Debra Dalgleish]

You're welcome! Thanks for letting me know that it worked for you.