Excel 2007 (Conditional Formatting) AND & IF

You don't need IF Statment and TRUEs and FALSEs withih a Conditional Formatting formula. By its nature the formula is conditional.

Try something like:

=AND(OFFSET(DataStart17D;COLUMN();ROW()-49;1;1)="PM";OFFSET(DataStart17D;COLUMN();ROW()-49;1;1)="AM")

Once you do so, you'll see an additional problem. The two statements in your AND function are mutually exclusive, so it will never evaluate to TRUE.

EDIT:

There does seem to be an issue with the AND statement and multiple OFFSET statements that use ROW or COLUMN

Please note that I'm going to use commas instead of semicolons as function parameter separators here, otherwise it's too hard to convert. You'll have to change the commas back to semicolons.

Also note that you didn't need the last two arguments in either the ROW or COLUMN function in your original question. You had them set to a height and width of 1, which is the default, and is optional.

Here's a simplified example:

If you do something like:

OFFSET(DataStart17D,ROW()+1,COLUMNS())="PM"

it will evaluate to TRUE in the worksheet and also in the conditional formatting

If you do something like:

=AND(OFFSET(DataStart17D,ROW()+1,COLUMNS())="PM",OFFSET(DataStart17D,ROW()+1,COLUMNS())="PM")

which is just repeating the same statement twice, it will evaluate to TRUE in the worksheet, but won't trigger the conditional format.

All of the above is just as you stated in your question. The answer that I think works is to use ROWS and COLUMNS instead, like this:

=AND(OFFSET(DataStart17D,ROWS($1:2)+1,COLUMNS($A:A))="PM",OFFSET(DataStart17D,ROWS($1:2)+1,COLUMNS($A:A))="AM")

The above would be the formula for A2. Note that the first row or column inside the parentheses is anchored with a dollar sign. This gives you the count of rows or columns from A1, effectively the same thing as the ROW or COLUMN function.