Allow Input if Adjacent Cell Contains Specific Text in Excel

In this article, we will learn how to allow any input in a cell if adjacent cell contains a specific text in a adjacent cell in Excel. We will be using Excel Data Validation, as you may have guessed. So let's begin.

Generic custom formula in Data Validation

=adjacent_cell= specific_text

We will write this formula in Data Validation's custom option. Not in a cell.

adjacent_cell = The cell in which you want to check the specified text. It can be any cell, but in this article we assume it for left adjacent cell of the entry cell.

Specific_text = The text that you want to check. It can be a hard coded text or a cell reference that contains the text.

You can select the cell where you want the data validation to be put on and then use the above generic Excel formula.

Enough of the theory. Let's have an Excel example to get it clear.

Example: Allow input in B column if A column contains specific text

Here I have an excel table. I am asking a question on the top and I want users to tell me if they have the answer. In column A, they select
N if they don't have the answer and they can select Y if they have the answer. If they select Y, then they can write their answer in column B, or else excel won't allow them.

Here's how you can use data validation to restrict the input if Column A does not contain Y text.

Select the range in column B where you want your data validation.
Now go to data-->Data Validation.
From the drop down menu, select Custom.
Now write this formula in the formula box:

=$A3="Y"
Uncheck the ignore blank option.

Hit OK. And it is done.

Now try to write anything in the B column. Excel will reject any input if you don't have Y in the A column of the table. Even if you have a blank cell in A column, column B will not take input until you put an Y in column A of the table.

Notes: It has all the weaknesses of data validation.

If you copy some random cell to this validated cell, it will overwrite the validations, and will stop working. It is better to protect the excel sheet, so that no user can alter the excel validations.

So yeah guys, this is how you can restrict the users from entering data in a cell based on another cell containing specific text. The query is complex but the solution is easy. I hope I was explanatory enough. However, if you have any doubts regarding data validations in Excel/2010/2013/2016/2019/365/VBA, let me know in the comments section below.

Related Articles:

Validation of text entries | The ISTEXT function of Excel can be used to validate the input is a text or not. To restrict the user from entering non-text values, we can use this with Excel Data Validation.

Excel Data Validation Between Dates | To ensure that the user enters the date between specified date range, use this Data Validation Formula.

Validate dates within next 7 days in data validation excel | Use Data Validation with TODAY function to ensure that the date entered is within next 7 days.

Popular Articles:

50 Excel Shortcuts to Increase Your Productivity | Get faster at your task. These 50 shortcuts will make you work even faster on Excel.

The VLOOKUP Function in Excel | This is one of the most used and popular functions of excel that is used to lookup value from different ranges and sheets.

COUNTIF in Excel 2016 | Count values with conditions using this amazing function. You don't need filter your data to count specific value. Countif function is essential to prepare your dashboard.

How to Use SUMIF Function in Excel | This is another dashboard essential function. This helps you sum up values on specific conditions.