Making Banded Rows Dynamic

In the last post we used conditional formatting to fill alternating rows in a range of rows.  However, there may be occasions when you want to use banded rows but only if the rows contain data.  This can be accomplished by changing the formula previously used.


For Excel 2007 select a cell in the range previously used and then from the Home tab select Conditional Formatting > Manage Rules > Edit Rule and change the formula to =AND(MOD(ROW(),2),COUNTA($A1:$E1)).

Click OK and the Apply


For earlier versions of Excel click Format > Conditional Formatting and change the formula as above

Click OK


Now the cells in your selected range are filled only when an entry is made in any cell in the odd numbered rows.

As always if you have any questions or comments you can enter them as a comment here on the blog or email me at the office.

Banded Rows

Occasionally there is a need when formatting a worksheet to use banded rows (i.e. cells in the odd rows color filled and the cells in the even rows not filled).  If the worksheet has only a few rows you can simply select the cells in the appropriate rows and format them with the desired color.  However, that's not practical when the sheet contains dozens or hundreds of rows.  When you have too many rows to do manually you can use conditional formatting.


Select the range of cells to which the formatting is to be applied.  For this example I've used the range A1:E10.

For Excel 2007 from the Home tab select Conditional Formatting > New Rule > "Use a formula to determine which cells to format".  Type the following formula in the "Format values where this formula is true" box:


=MOD(row(),2)


Click the Format button and select the Fill tab and then select the fill color.

Click OK

Click OK and your worksheet range now has banded rows.

In the future we'll look at how to make the rows dynamic so when you add data to rows after row 10 the conditional formatting will expand to the newly populated rows.


As always if you have any comments or questions, please comment here or email me at the office.

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.

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.