CODEWITHSUNDEEP
excelexcel-formulaexcel-2013conditional-formatting
Nitemare
Nitemare

Reputation: 3

Formula works in cell but not in conditional formatting

I have a list of Phone Extension, with their assigned phone number, and the users name, if in use. I'm trying to use conditional formatting to find extensions with a phone number, but no user, and highlight them in green.

I tested my formula in a cell first, and it works there, but when i put it in to conditional formatting for all of Column B. Nothing happens.

Here is my formula

=IF(AND(NOT(OFFSET(INDIRECT(ADDRESS(ROW(), COLUMN())),0,-1)=""), 
            OFFSET(INDIRECT(ADDRESS(ROW(), COLUMN())),0, 1)=""), TRUE, FALSE)

                 Current Outcome                                 Goal Outcome

Upvotes: 0

Views: 936

Answers (2)

Slai
Slai

Reputation: 22866

Here is a way that might make it easier. In the Excel Options, Formulas, check R1C1 reference style. Now you can select the range and use this R1C1 Formula:

=AND(RC[-1]<>"",RC[1]="")

When you uncheck "R1C1 reference style", the conditional Formating Formula will be back to A1 style:

=AND(A2<>"",C2="")

Upvotes: 0

elmer007
elmer007

Reputation: 1445

How about the following formula:

=AND(NOT(ISBLANK(A2)),ISBLANK(C2))

This uses the AND function to check if two conditions are true:

  1. Cell A2 is not blank- NOT(ISBLANK(A2))
  2. Cell C2 is blank- ISBLANK(C2)

Set this up for cell B2's conditional formatting. Then, go to Conditional Formatting > Manage Rules... and change the "Applies to" range to cover all of your column B values (e.g., =$B:$B)

Upvotes: 1

Related Questions