data validation - display dropdown list or else NA

xlhelppls

New Member
Joined
Jun 17, 2016
Messages
14
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!
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,450
Office Version
  1. 2016
Platform
  1. Windows
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
EFGHIJK
1OptionLoVPlanlistNA
2PromoNAPlanANA
3PlanB
4PlanC
5PlanD
6
Sheet1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
F2Expression=OR(AND(E2<>"Promo",F2<>"NA"),AND(E2="Promo",F2="NA"))textNO
Cells with Data Validation
CellAllowCriteria
F2List=IF(E2="Promo",Planlist,NA)
 

Watch MrExcel Video

Forum statistics

Threads
1,129,930
Messages
5,639,054
Members
417,067
Latest member
rohitbabshet

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top