WPS Spreadsheets IF Formula for Yes, No, N/A, and Blank Data Validation Lists
Question details
The user needs an IF formula that responds to Yes or No selections and controls whether a dependent cell is blank or displays N/A.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- A worksheet uses a Yes/No drop-down in one cell and needs dependent cells to remain blank for Yes or display N/A for No, while potentially allowing later list selections.
- Observed behavior
- A formula can return a blank string for Yes and N/A for No, but entering a manual value from a data-validation list in that same cell overwrites the formula.
Decide which cells will be user-input cells and which will be formula-result cells. Confirm that the Yes/No source cell uses the exact text values Yes and No, including capitalization and spaces.
Use separate input and result cells with an IF formula
Use this approach when the dependent result only needs to display a blank or N/A value based on the first selection.
A formula-driven cell cannot also preserve its formula after a user selects a different value from its data-validation list. Keep the manual selection in one column and place the IF result in a separate column.
Select the source cells, open the Data tab, choose Data Validation, set Allow to List, and enter Yes,No as the list source.
Select the first dependent result cell, such as B2, and enter =IF($A2="No","N/A",IF($A2="Yes","","")) . Press Enter, then fill or copy the formula down the column.
Choose Yes from the drop-down in A2 and confirm that B2 appears blank. Choose No and confirm that B2 displays N/A.
If users must select additional values from a list, create that validation list in a separate input column instead of placing it in the formula cell.
Use VBA when the same cell must be automatic and manually selectable
Use this advanced option only when the same dependent cell must both react to the first answer and accept later user selections.
Build Yes, No, N/A, and blank workflows in WPS Spreadsheets
WPS Spreadsheets lets you combine Data Validation drop-down lists with IF formulas in separate input and result cells. This keeps formulas intact while automatically showing N/A or a blank result based on the user’s selection.
- 1. Add the source drop-down: Open the worksheet in WPS Spreadsheets, select the source cells, then choose Data > Data Validation and create a Yes,No list.
- 2. Add the conditional formula: In a separate dependent cell, enter =IF($A2="No","N/A",IF($A2="Yes","","")) and adjust the cell reference to match your layout.
- 3. Fill the formula down: Copy the formula to the remaining dependent rows using the fill handle or copy and paste.
- 4. Verify the workflow: Select Yes and No from the source list and confirm that the dependent cells become blank or show N/A without losing their formulas.
Frequently Asked Questions
Why does my IF formula disappear after I choose a value from the drop-down list?
The selected data-validation value replaces the cell contents, including the formula. Put the drop-down selection in one cell and the IF formula in a separate result cell.
What formula shows N/A for No and a blank for Yes?
If the Yes/No value is in A2, use =IF($A2="No","N/A",IF($A2="Yes","","")) in the dependent result cell.
Can a formula cell also allow users to choose values from a list?
Not reliably with standard data validation. A manual selection overwrites the formula, so use separate input and result cells or a VBA macro for a same-cell workflow.
How do I make the formula work for multiple rows?
Use a relative row reference such as $A2, enter the formula in the first result row, and fill it down. The row number will update automatically while column A remains fixed.
What happens if the source cell is empty or contains another value?
The nested formula returns a blank for any value other than No because its final condition is also an empty string. Add another IF condition if other source values need different results.
