1

I'm formatting a calendar on multiple sheets in excel, with the weekends highlighted. I'd like to format all the sheets from a table of 1s & 0s I generated to match different years. Unfortunately, I've failed to properly use named ranges or offset to achieve the effect I desire.

The left formatting uses a relative reference, =O3=1, which would be cumbersome and introduce the possibility of error as others copy in the 'data' onto 13 sheets in years hence. So, I noticed people suggesting using a named range. Here, I could not reproduce their results.

The image above uses the O3:O14 named bhbh (since I'm just practicing on an empty sheet) and attempts to format the Dec column where =bhbh>0. As you can see, the entire area is colored rather than by cell within the range.

The same happens when the grid is a named range. Using =offset("sheet3!O3:O10")=1 does not apply the formatting to the selected cells. I'm at a loss besides pasting the data on each sheet for relative formatting, but that seems dangerous over time.

1
  • To answer my own question (in case someone stumbles into this thread), Excel is checking whether all the values in bhbh are collectively greater than 1, hence the format is applied on all the cells. - To achieve the effect I desire without using Mike's solution below, I would have had to name each cell and then apply a cell by cell format across all the sheets. Obviously the worst solution of the three. Oct 27, 2010 at 7:51

1 Answer 1

1

You can acheive this without needing the additional table of 1s and 0s or named ranges...

Method:

Step 1:

Set up your sheet as shown below...

+-------+-------+-------+-------+-------+-------+---
|       |   A   |   B   |   C   |   D   |   E   |
+-------+-------+-------+-------+-------+-------+---
|    1  |  2010 |  Jan  |  Feb  |  Mar  |  Apr  | ...
+-------+-------+-------+-------+-------+-------+---
|    2  |     1 |       |       |       |       |
+-------+-------+-------+-------+-------+-------+---
|    3  |     2 |       |       |       |       |
+-------+-------+-------+-------+-------+-------+---
|    4  |     3 |       |       |       |       |
+-------+-------+-------+-------+-------+-------+---
|       |   ... |       |       |       |       |

Step 2:

Select the area you want to apply the conditional formatting (B1:M32) making sure that cell B1 is the active cell in the selection.

Step 3:

Apply a conditional conditional format using the option Use a formula to determine which cells to format and use this formula, taking care to make sure the absolute references are entered correctly. Select your formatting of choice.

=WEEKDAY(DATEVALUE($A2&" "&B$1&" "&$A$1),16) < 3

Explanation:

This works by using the day (col A), the month (row 1) and the year (cell A1) to build a date string, then convert it to a real date (using the DATEVALUE function) and then determining if that date is a Saturday or Sunday (using the WEEKDAY function).

Note: Tested in Excel 2010

1
  • Excel 07 (what I'm using) & 03 (target user) don't need the parameter you've left as 16, but otherwise this works as promised. That is embarrassingly easy. Thanks Mike Oct 27, 2010 at 7:48

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .