Prevent Selection Based on Value in Another Cell (cont.)

In the previous post we looked at using conditional formatting to "hide" selections in a cell based on selections made in another cell and in this post we'll use conditional formatting to provide the user with cells for data entry based on the selections in those same cells.


In the image below the selection for "MyHD Software" has been changed to "Y". When "Y" is selected the user is required to provide additional information for "Num Users" and "# Locations".  Since the user has not entered "Y" for "RF Warehouse Manager (w/HD)" the cells for "Num Users" and "# Locations" are filled Black.  If "MyHD Software" is "Y" then "RF Warehouse Manager (w/o HD)" is not available and is filled Black.

If the user has selected "Y" for "RF Warehouse Manager (w/o HD)" the "Num Users" and "# Locations" are still required and entries for "MyHD Software" and "RF Warehouse Manager (w/HD)" are invalid.

"Num Users" is in cell D7 and contains the formula =IF(OR(C8="Y",C10="Y"),"Num Users","") which displays the heading when either "MyHD Software" or "RF Warehouse Manager (w/o HD)" has been selected with a "Y".  Cell E7 uses the same formula with "# Locations" substituted for the text =IF(OR(C8="Y",C10="Y"),"# Locations","").


The range $D$8:$E$9 have two conditional formats applied (below).  The first fills the cells Black when "RF Warehouse Manager (w/o HD)" is selected ($C$10 = "Y").  The second places a single line border around the cells in the row where a "Y" is entered in column "C" (i.e. when "Y" is entered in cell C10 as above then cells D10 and E10 have single line borders.  The range of cells D8:E9 do not have single borders but this is not seen because they are filled Black).

The end result is that the user is presented with cells for data based on the selections made in column C.


Unfortunately, filling a cell Black does not prevent a user from entering a value.  Below is how our example displays if the user selects "Y" from the drop down in C9 when "N" is selected in C8.  Normally a user will not do that simply because it "appears" that a selection cannot be made in this cell but there is actually nothing preventing it being done.

To prevent this we can change the values available in cell C9.  In the example above the Data Validation below is used and provides a list of Y and N for the drop down.

We can use a dynamic list of values in order to limit the available selection.
In cell M1 the heading "List" is entered.  The formula =IF(C8="N","N","Y") is entered in cell M2 and =IF(C8="N","","N") is entered in M3.  When the value in C8 = "N" cell M2 will = "N" and M3 will be blank.  When the value in C8 = "Y" then cell M2 will = "Y" and M3  will = "N".


Next we want to adjust the length of our "list" to match the entries.  In cell L1 the heading "List Len" was entered.  Cell L2 contains the formula =IF(C8="N",1,2) which results in a value of 1 when C8 = "N" and a value of 2 when C8 = "Y".


Now we create a dynamic named range.  For Excel 2007 select the Formulas tab and then Define Name.  For Excel 2003 and earlier select Insert > Name> Define to display the New Name box.  Enter the desired name (I used validList) and then enter the formula =OFFSET($M$2,0,0,$L$2,1) and click OK.

The Data Validation for cell C9 was opened and changed from Y, N (above) to use the named range validList as below.

Now when the value in cell C8 = "N" the drop down in cell C9 only displays a  value of "N" but when C8 = "Y" the drop down in C9 displays "Y" and "N".

As always if you have and questions or comments regarding this post, please, comment here or email me at the office.