Prevent Selection Based on Value in Another Cell

I've been working on a workbook for our Sales Department that contains an element that might be of interest especially if your company uses Excel workbooks for inputting data rather than just using it to extract data from your ERP database.

One of the purposes of this to allow the selection of options regarding the HD software and our Value Add Products.  The selection values for all the products is a simple Y/N but based on the selection of a particular product other selection options may change (i.e. If MyHD Software is selected the user cannot select "RF Warehouse Manager w/o HD").  Additionally some selections require additional fields for input of data to be displayed.

The image below has all options set to "N".  Since the user should not be able to select "RF Warehouse Manager (w/HD)" if the "MyHD Software" had not been selected conditional formatting has been used to prevent the user from "seeing" the cell.  

If you're using Excel 2007, on the Home tab click Conditional Formatting > New Rule > Use a formula to determine which cells to format.  In this case the we want to condition the Y/N cell related to RF Warehouse Manager (w/HD) to be dependent on the Y/N entry for MyHD Software so we enter the formula =C8="N".  Now click the "Format..." button and from the Fill tab select Black and click OK.

If you're using Excel 2003 click Format > Conditional Formatting

Click the drop down and select "Formula Is".  Enter the formula =C8="N".  Click the "Format..." button and from the Patterns tab select Black.  Click OK and then click OK again.

Now whenever the condition C8="N" evaluates to True the cell will be Black.

I've used this method of prevention many times and it works okay but does not actually prevent the user from entering or selecting a value in the target cell.  If you want to prevent the user from entering an invalid entry in Cell C9 you can use a Dependent Drop Down List.  This presents the user with a drop down list in cell C9 that is dependent on the value in cell C8.  

We'll look at how to do that in the next post.

As always, if you have any comments or questions (no one ever does) you can  comment here or email me.