Locking a cell based on the contents of another cell

At times, you may require a cell not to allow entry depending on the contents of another cell. This can be achieved with the use of Data Validation.

Looking for Microsoft Excel training? Click here for our courses.

  

Using Data Validation and a formula to test cell contents

To have the Data Validation feature control a cell based on the contents of another cell, you will need to write a formula into Data Validation to point at and test the other cell.  The result of the test will be TRUE or FALSE and this in turn will dictate whether entry into the cell is allowed (TRUE result) or denied (FALSE result).

For example, a formula such as this one...

=A2="Yes"

...will either be true (that A2 does indeed equal "Yes") or false (that A2 does not equal "Yes").

You may use a more complex formula to test the contents of a cell, but the result will be the same, either the test result is TRUE or the test result is FALSE (which in turn, controls whether Data Validation allows entry into the cell or denies entry into the cell).

To do this, follow these instructions:

Click the cell you want people to be allowed or not allowed to type into.

Click the Data tab then click the top-half of the Data Validation button.

On the Settings tab, choose Custom on the Allow drop-down menu.

In the Formula field on the panel, type a formula to test a cell eg: =A2="Yes"

Click OK.

The cell will now only allow users to enter data into the cell if the formula is true.

You may decide to have many cells with Data Validation all looking at one cell (so one cell controls them all) or you may decide to have each individual cell controlled by another individual cell with individual Data Validation formulas.  The application of this is up to you.

  

Modifying the warning panel

You may decide you would like a more personalised message to appear if a user tries to type into a cell locked by Data Validation than the default "The value you entered is not valid" message.  To personalise this message, follow these instructons:

Click the cell you wish to modify the "Denied" message for.

Click the Data tab then click the top-half of the Data Validation button.

On the Error Message tab, type the title for the message in the Title field and the error message in the Error Message field.

Click OK.

Now when entry into the cell is denied due to a FALSE result in the custom Data Validation formula, your customised message will appear instead of the default message.

  

Nicholas Jankovic

T7 Training Systems

  

Wednesday, 1st August 2012

 

HomeInformation and pricingCoursesMake a bookingBlogContact us
© 2011 T7 Training Systems

Magicdust Responsive Website Design

HomeInformation and pricingCoursesMake a bookingBlogContact us