CODEWITHSUNDEEP
excel-formula
NSN
NSN

Reputation: 750

Excel Formula vs Formula used in conditional format

I have a excel formula which correctly returns true. But when I used the same formula in conditional format, I am not able to get the conditional format. Please help.

=AND(FIND("MyText",INDIRECT(ADDRESS(1,COLUMN()))),INDIRECT(ADDRESS(ROW()+2,COLUMN()))=1)

If the first cell of the column is "My-text" and the value is 1 (in row +2, same column) the formula correctly returns true. If I used it in a conditional format to paint a fore-ground color it is not working. I also tried search and searchb. But didnt work. The format of the cell (row() +2, column) that might have value 1 is "General"

Upvotes: 0

Views: 116

Answers (3)

ian0411
ian0411

Reputation: 4275

Not sure why this wouldn't work. But you can simply replace the AND function with *. Here is what you can try:

=FIND("MyText",INDIRECT(ADDRESS(1,COLUMN())))*INDIRECT(ADDRESS(ROW()+2,COLUMN()))=1

Upvotes: 1

Slai
Slai

Reputation: 22886

If you change to R1C1 reference style, you can enter the formula as =AND(R1C="MyText", R[2]C=1)

R1 means row 1, C means same column, and R[2] means 2 rows below the current cell.

In A1 reference style, the formula will depend on the currently active cell, so if for example the active cell is A1, the formula will be =AND(A$1="MyText", A3=1)

Upvotes: 1

NSN
NSN

Reputation: 750

I changed the AND into following and it worked. But not sure what is the issue with AND in conditional format

=NOT(ISERROR(SEARCH("MyText",INDIRECT(ADDRESS(1,COLUMN()))))) *INDIRECT(ADDRESS(ROW()+2,COLUMN()))

Added the above answer for reference.

Upvotes: 0

Related Questions