Restricting Input Based on Adjacent Cell’s Specific Text in Excel
Restricting Input Based on Adjacent Cell’s Specific Text in Excel. In this tutorial, we will learn how to restrict input in a cell based on the content of an adjacent cell in Excel using Data Validation. Let’s get started.
Restricting Input Based on Adjacent Cell’s Specific Text in Excel
- Generic Custom Formula for Data Validation: We will use the following formula in Data Validation’s custom option (not in a cell):
Restricting Input Based on Adjacent Cell’s Specific Text in Excel
=adjacent_cell=“specific_text”
- adjacent_cell: Refers to the cell you want to check for the specified text. In this example, we assume it’s the left adjacent cell of the entry cell.
- specific_text: The text you want to check. It can be a hard-coded text or a cell reference containing the text.
- Excel Example: Allowing Input in Column B Only if Column A Contains Specific Text Imagine an Excel table where you ask a question at the top, and users respond with “Y” in column A if they have the answer, and “N” if they don’t. If they select “Y,” they can write their answer in column B; otherwise, input in column B should be restricted.
Restricting Input Based on Adjacent Cell’s Specific Text in Excel
Here’s how you can achieve this using data validation:
- Select the range in column B where you want the data validation to apply.
- Go to “Data” –> “Data Validation.”
- Choose “Custom” from the drop-down menu for validation criteria.
- Enter the following formula in the formula box:
=$A3=“Y”
- Uncheck the “Ignore blank” option.
- Click “OK” to apply the validation.
Now, if you try to input anything in column B without having “Y” in the adjacent cell (column A), Excel will reject the input. Even if column A is blank, column B will not accept input until “Y” is entered in column A.
Note: Data validation has its limitations. If you copy content from another cell into a validated cell, it will overwrite the validation and stop working. To protect the Excel sheet from users altering the validations, consider using sheet protection.
By following these steps, you can effectively restrict users from entering data in a cell based on the presence of specific text in another cell.
