Do not allow blank, if..

QuestionEdit

If I have 2 Column A,B, in Column " A ", only "Y", "N" are the allowed Values. now Column "B " should not be empty if Column "A " = " N".

Question 1 = how to restrict that if Column A = N, Column B is not blank with out macros Question 2 = set an error message to appear to illustrate above.

AnswerEdit

To Restrict User From Typing Other Characters:- Place the Cursor in A1 cell and hit Cntrl+Spacebar which will select the whole Column A and the A1 will be the Active cell (Active Cell will have a white Background after selection also).

Select Data>>Validation>>Settings>>Validation Criteria>>Select CUSTOM from the Drop Down>>Under the Formula copy and paste the below formula.

=OR($A1="Y",$A1="N")

Move on to the ERROR ALERT Tab on the same Data Validation Dialog box and follow the instructions.

Error Alert>>Make sure whether the ¡°Show Error Alert After Invalid Data Is Entered¡± is having a Tick Mark.

Error Alert>>Style>>Select the STOP from the Drop Down List.

Error Alert>>Title>>Under the Title copy and paste the below Text. INVALID INPUT

Error Alert>>Error Message>>Under the Error Message copy and paste the below Text:-

Input Value should be "Y" or "N"

and Give Ok..

Now try to enter some value in column-A other than "Y" and "N".

(The reason for requesting you to place the Cursor in A1 cell while selecting the whole Column A is because, the above formula is referring the cell A1 so the Active cell should be in A1 cell while applying the validation. If suppose if you have located your cursor (Active cell) in A3 cell and selected the whole column (Column A) and applying the above formula won¡¯t let Validation to work properly) WITHOUT USING MACRO we can¡¯t able force the cursor to move to another cell if the active cell meets our criteria. Instead of thinking how to forceto go to next cell to fill a value if our Active cell meets our criteria without using the macro, we just put the below if function in B1 cell. =IF($A1="N","Fill This Cell","") Drag the B1 cell formula to the remaining cells based on Column-A Input Range. Now the Column-B cell will intimate (¡°Fill This Cell¡±) you to enter a value if Column-A cell is equal to¡±N¡±, otherwise it will show blank.

Hope it¡¯s clear!

Click or Copy and paste the below link in web browser to download the Example file.

http://www.sendspace.com/file/vxkx1k

Scroll the mouse to the bottom and click Download.