how to remove double quotes from within a formula

[burnsb]

I am trying to conditional format a section of a row based on the value of the a cell. Specifically I am trying to grey out a section of a row (A#:AT#) when the QTY cell (B#) is 0.

I am been trying to create a formule that will test for this condition so that I could make a single conditional formatting rule that would cover many rows. The problem I am having is that my formula is constantly returning FALSE due to past of the formula having double quotes around it.

Here is the formula that I created:
=EXACT(CONCATENATE("B",ROW()),0)

When this formula is evaluated I get:
=EXACT(CONCATENATE("B",4),0)
=EXACT("B4",0)
=FALSE

The formula =EXACT(B4,0) returns a TRUE value, so I believe all I need to do is figure out how to remove the double quotes from around the "B4" part of the formula.

Any ideas or suggestions?

 

[gsistek]

You could try =EXACT($B4,0)

 

[pgc01]

Hi

For row 4 try:

=($B4=0)

 

[burnsb]

Hi

For row 4 try:

=($B4=0)

The problem with this is that it only works for row 4. I want to create a formula that works for any row, to avoid having to create a conditional formatting rule for each row.

 

[gsistek]

That is why there is no dollar sign before the four. If you highlight the whole area you want to conditionally format (starting with the upper left hand cell), when you apply the formula as $B4 instead of $B$4, the row will no longer be absolute. It will adjust relative to the row that it references.

 

[burnsb]

That is why there is no dollar sign before the four. If you highlight the whole area you want to conditionally format (starting with the upper left hand cell), when you apply the formula as $B4 instead of $B$4, the row will no longer be absolute. It will adjust relative to the row that it references.

I don't think your solution works for every row considering the QTY cell changes for every row. However I did discovered how to remove the quotes:
=EXACT(INDIRECT(CONCATENATE("B",ROW())),0)

Thanks for your suggestions

 

[pgc01]

Hi

Assuming your data starts in row 4 you could select the data and use in the conditional formatting the formula I posted:

=($B4=0)

Like gsistek said, the formula uses a relative address and so is valid for all rows. It is, however, your choice, and if you prefer

=EXACT(INDIRECT(CONCATENATE("B",ROW())),0)

, although less efficient and more complex, it may also work.

 

[burnsb]

Hi

Assuming your data starts in row 4 you could select the data and use in the conditional formatting the formula I posted:

=($B4=0)

Like gsistek said, the formula uses a relative address and so is valid for all rows. It is, however, your choice, and if you prefer

=EXACT(INDIRECT(CONCATENATE("B",ROW())),0)

, although less efficient and more complex, it may also work.

your correct, thanks