ROW function does not work as expected

[Nacht Klavier]

Hello,

I am trying to conditionally format a cell based on two values: the
value of another cell and the value of this cell itself. I am using
the following:

=IF(AND(INDIRECT(ADDRESS(ROW();3))="B";D6>0);TRUE;FALSE)
Check if in the current row at column 3 the value is "B" and if the
value of this cell is greater than 0

This does work if I write it directly in a cell. If I use the exact
same formula in the conditional formatting dialog, nothing happens. I
learned that in Excel 2003 there was a bug concerning the INDIRECT and
AND function (http://groups.google.de/group/microsoft.public.excel/
browse_thread/thread/
9973d2120f5fad15/13b37943e529dd96#13b37943e529dd96).

So I changed it to:
=IF(AND(OFFSET(A1;ROW()-1;3-1)="B";D6>0);TRUE;FALSE)

but again, it fails. Replacing ROW() with a value like "6" and
everything is fine.

Does anyone has a solution/workaround for this?

Thanks.
NK

 

[Jordon]

If the current cell is C3, this works for me.

=IF(AND(B3="B",C3>0),TRUE,FALSE)

 

[Nacht Klavier]

Hi Jordan,

Thanks for your lightning fast answer.

I should add, that conditional formatting will be used in a table like
this:

B 1 1 1 0 0 1 <-- format all zeros with color grey, all ones with
color green
<other values>
<other values>
B 0 0 0 1 1 0
<other values>
<other values>
X 0 0 0 1 1 0
<other values>
<other values>

The ROW function is intended to help to only have two formulas for
multiple ranges.
Btw: I am using Excel 2007.

NK

 

[Jordon]

Can't you just have two conditions?

=IF(AND($B3="B",C3>0),TRUE,FALSE)
=IF(AND($B3="B",C3=0),TRUE,FALSE)

 

[Dave Peterson]

Just curious why you don't refer to B(row) directly.

You know what cell is getting the conditional formatting, so why use =indirect()
at all???

So if a cell on row 6 is getting the conditional formatting:

=and(C6="B";d6>0)
or maybe:
=and($C6="B";d6>0)

if there are multiple cells, but you always want to check column C.

 

[Nacht Klavier]

Hello all,

The reason for not using "C" as a fixed value is that I want to set
formatting for multiple rows - every third one - in a table (see my
second post). In Excel I am able to select only the rows I need and
apply the formula for formatting there. If I manually set the row
within the formula it is fine. But this is not a nice solution, is it?

Thanks,
NK

 

[Dave Peterson]

I don't understand.

If you're selecting the range to apply conditional formatting, you can select
every third row and use a conditional formatting formula like:

=and($C##="B";$d##>0)

Where ## represents the row that the activecell is in.

Excel is pretty smart and will adjust the row numbers for all the cells in the
selected area.

It's a lot like when you copy a formula down a column. Excel is smart enough to
adjust the formula -- as long as you write that formula correctly with the $
signs in the correct spots!

 

[Claus Busch]

Hi NK,

Am Sun, 25 Jul 2010 12:48:20 -0700 (PDT) schrieb Nacht Klavier:

The reason for not using "C" as a fixed value is that I want to set
formatting for multiple rows - every third one - in a table (see my
second post). In Excel I am able to select only the rows I need and
apply the formula for formatting there. If I manually set the row
within the formula it is fine. But this is not a nice solution, is it?

your values start in A1, then:
=AND(MOD(ROW(),3)=1,A1=1)
for formatting the ones


Regards
Claus Busch

 

[Nacht Klavier]

Hello all,

Thank you for your help. I have the solution now:

--> Simply save, close and reload.

Yes, my formula works as expected now - but not when I change it
without closing the file. This is quite an unexpectable error but now
everything is fine.

NK