Using Logical cell references (XP)

Topic

New Reply

If this works:

=IF((CELL(“contents”,H12)=””),”blank”,”NOT blank”)

and so does this (as part of a longer formula) to refer to the cell adjacent and to the right of the current cell:

ADDRESS(CELL(“row”),CELL(“col”)+1)

then why can’t I substitute H12 in the first formula to a logical value like this:

=IF((CELL(“contents”,ADDRESS(CELL(“row”),CELL(“col”)+1)=””),”blank”,”NOT blank”)

what I get is a formula error… I’m hoping it’s something simple that I haven’t spotted.

In case you are wondering I want to use conditional formatting and it is predicated on the adjacent, right cell being blank (or not)

many thanks

liz

Reply | Quote

Replies

You don’t need such convoluted formulas (I think) for conditional formatting. If you specify Formula Is in the first dropdown list in the Conditional Formatting dialog, and use relative references in the formula, they will adapt themselves automatically.

Example: select D3:D12, then select Format | Conditional Formatting…
Select Formula Is, then enter =E3>0 (take care not to use $E$3), and click OK, the condition will refer to the cell to the right of the cell to be formatted: in D4, it will refer to E4, etc.

Or do I completely misinterpret what you want?

Reply | Quote

Hans,

Thnak you for this simple solution, however I can’t seem to do a multiple condition test… probbaly me being stupid! I want to say for cell A1 where B2 is not blank and A1 is blank highlight A1, is this possible?

liz

Reply | Quote

You can use the AND function to combine conditions. In your example, you would select Formula Is from the dropdown lisr, and enter
=AND(ISBLANK(A1),NOT(ISBLANK(B2)))
in the box next to it. Another formula with the same effect is
=ISBLANK(A1)*NOT(ISBLANK(B2))

Reply | Quote

You can use the AND function to combine conditions. In your example, you would select Formula Is from the dropdown lisr, and enter
=AND(ISBLANK(A1),NOT(ISBLANK(B2)))
in the box next to it. Another formula with the same effect is
=ISBLANK(A1)*NOT(ISBLANK(B2))

Reply | Quote

Hans,

Thnak you for this simple solution, however I can’t seem to do a multiple condition test… probbaly me being stupid! I want to say for cell A1 where B2 is not blank and A1 is blank highlight A1, is this possible?

liz

Reply | Quote

You don’t need such convoluted formulas (I think) for conditional formatting. If you specify Formula Is in the first dropdown list in the Conditional Formatting dialog, and use relative references in the formula, they will adapt themselves automatically.

Example: select D3:D12, then select Format | Conditional Formatting…
Select Formula Is, then enter =E3>0 (take care not to use $E$3), and click OK, the condition will refer to the cell to the right of the cell to be formatted: in D4, it will refer to E4, etc.

Or do I completely misinterpret what you want?

Reply | Quote

ADDRESS returns the reference as a string, not a true cell reference. Try:

=IF(ISBLANK(INDIRECT(ADDRESS(ROW(),COLUMN()+1))),”blank”,”NOT blank”)

Reply | Quote

ADDRESS returns the reference as a string, not a true cell reference. Try:

=IF(ISBLANK(INDIRECT(ADDRESS(ROW(),COLUMN()+1))),”blank”,”NOT blank”)

Reply | Quote

After seeing Steve’s reply, I see I missed that you plan to use conditional formatting. In that case, =ISBLANK() by itself should do what you want, since the formatting will be applied if the condition is TRUE. So if your conditional format is in cell A1, your CONDITIONAL FORMAT FORMULA would be for =ISBLANK(B1) (no $’s), and set the A1 formatting required when B1 is blank.

Reply | Quote

It was Hans not Steve

Steve

Reply | Quote

You guys have the same accent.

Reply | Quote

I know we type very similar answers (and many times even simultaneously the same), but I doubt we have the save accent

Steve

Reply | Quote

You don’t speak Double Dutch?

Reply | Quote

I don’t even speak Single Dutch.

Steve

Reply | Quote

I don’t even speak Single Dutch.

Steve

Reply | Quote

You don’t speak Double Dutch?

Reply | Quote

I know we type very similar answers (and many times even simultaneously the same), but I doubt we have the save accent

Steve

Reply | Quote

You guys have the same accent.

Reply | Quote

It was Hans not Steve

Steve

Reply | Quote

After seeing Steve’s reply, I see I missed that you plan to use conditional formatting. In that case, =ISBLANK() by itself should do what you want, since the formatting will be applied if the condition is TRUE. So if your conditional format is in cell A1, your CONDITIONAL FORMAT FORMULA would be for =ISBLANK(B1) (no $’s), and set the A1 formatting required when B1 is blank.

Reply | Quote