Dropdown Box Based on IF Statement

[arstearns]

Hello,

I have a spreadsheet where I would like to have two different possibl
dropdown boxes available in column D based on the existence of a tex
string in column C. In other words, if the string exists, use dropdow
box 1. If the text does not appear use dropdown box 2.

I know how to create the two dropdown boxes using Data Validation and
know how to create an IF Statement to identify the text that wil
determine whether dropdown box 1 or dropdown box 2 will appear, but
do not know how to link the two. Any thoughts?

I am not a VB Programmer.

Warmly, Al Stearns
Brevard, N

 

[Frank Kabel]

Hi
have a look at
http://www.contextures.com/xlDataVal02.html
for dependent listboxes

 

[arstearns]

Hello Frank,

Thank you for your quick response. I did find that link you mentione
in my research prior to posting here. It gives instructions fo
changing the contents of a 2nd dropdown box depending on the choic
made in a 1st dropdown box.

I need to change the contents of one dropdown box (or choose one of tw
dropdown boxes) depending on the existence of a text string in anothe
cell.

Warmly, Al Stearns
Brevard, North Carolina
US

 

[Debra Dalgleish]

Create two source lists (NameListA and NameListB)
Select cell D2
Choose Data>Validation
Choose Allow: List
In the Source box, enter the formula:
=IF(C2="Valid",NameListA,NameListB)
replacing "Valid" with your text string

 

[arstearns]

Hello Debra,

Thank you for your fine response! We are close.

The string I mentioned is actually a substring. In other words, I a
looking for the substring "Payment" in column C. Column C will alway
start with this substring but will look like: "Payment - Inv 423".
tried "Payment*" and "*Payment*" but these do not provide the correc
list. Such wildcards do work with SUMIF.

Also, once the above problem is solved I will be testing on a few cell
at the top of an existing spreadsheet. Is there a way to "copy" thi
Data Validation to all the cells below without deleting the cell
contents (manually filled in without dropdown lists)?

Warmly, Al Stearns
Brevard, North Carolina
US

 

[Frank Kabel]

Hi
change the formula
=IF(C2="Valid",NameListA,NameListB)

to
=IF(ISNUMBER(FIND("Payment",C2)),NameListA,NameListB)

 

[arstearns]

Hello Frank,

Thanks so much for the key to my problem!

=IF(ISNUMBER(FIND("Payment",C2)),NameListA,NameListB)

As I understand it, the FIND("Payment",C2) function tries to locate th
substring "Payment" in the cell. If it finds it, a 1 is returned. If i
does not find it a #VALUE! error value is returned.

The ISNUMBER function evaluates the result of the FIND statement. I
"Payment" was found, a 1 is returned and ISNUMBER is true with Dat
Verification using NameListA. If "Payment was not found, a #VALUE
error value is returned which is not a number which causes ISNUMBER t
be false with Data Verification using NameListB.

Many thanks to both Debra and Frank.

I have figured out how to copy Data Verification settings to othe
cells already containing data and will share it here for the benefit o
others.

1) Copy the cell with the desired Data Verification settings.
2) Highlight the cell or cells to copy to.
3) Select Edit, PASTE SPECIAL.
4) Click on the Validation radio button
5) Select OK.

Warmly, Al Stearns
Brevard, North Carolina
US

 

[Rob]

Also, once the above problem is solved I will be testing on a few cells

at the top of an existing spreadsheet. Is there a way to "copy" this
Data Validation to all the cells below without deleting the cells
contents (manually filled in without dropdown lists)?

Warmly, Al Stearns
Brevard, North Carolina
USA

Found this in the Help section. Not sure which version of eXcel you're
using. This is available in Office XP.

1. Click the cell with the settings you want to copy, and click Copy .
2. Select the cells for which you want to duplicate the settings.
3. On the Edit menu, click Paste Special.
4. Click Validation

Cheers,

V