data validation - display dropdown list or else NA

[xlhelppls]

I have been playing with data validation for this scenario and trying to get the "NA" value to be displayed in the cell versus having user to still click the dropdown list to select it.

Scenario -- if cell E2 = "promo" then I need a list of values displayed in the dropdown list; if E2=anything else, I want NA to be displayed in the cell vs user having to click on dropdown list and click NA.

I created named ranges so that PlanList in the formula contains the values for the dropdown list and then NA is also a named range that just contains NA. I tried to put "NA" in the below formula with double quotes, but I couldn't get it to work, and thus why I also did a named range of NA.

This is my data validation (list option) formula - =IF(E2="Promo",PlanList,NA)

Is there another option to use in order to get the dropdowns to display when the right value is in cell E2 but then cell F2 would have the value of "NA" when 'promo' is not in E2?

thanks much in advance!

 

[Toadstool]

Hi XLHelpPls,

Without using VBA then F2 can't contain a formula and be a Data Validation List. The closest you could get is to highlight using Conditional Formatting, like this:

XLHelpPls.xlsx
	E	F	G	H	I	J	K
1	Option	LoV			Planlist		NA
2	Promo	NA			PlanA		NA
3					PlanB
4					PlanC
5					PlanD
6
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F2	Expression	=OR(AND(E2<>"Promo",F2<>"NA"),AND(E2="Promo",F2="NA"))	text	NO

Cells with Data Validation
Cell	Allow	Criteria
F2	List	=IF(E2="Promo",Planlist,NA)

 

[xlhelppls]

Thanks, Toadstool. I will give that a try.