Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.1k views
in Technique[技术] by (71.8m points)

excel - Conditional formatting: using row() inside indirect() inside and() doesn't work. What am I doing wrong?

Say I have set a value of 55 on cell B2.

If it's value is 55, the cell's background color should be green. So I created a new rule using a formula by selecting "User a formula to determine which cells to format" and set the formula to the following:

=B2 = 55

It worked, obviously. But because we don't want to hard-code the row because we are using this spreadsheet as a template in an application that generates more rows by simply copying that cell to another row, I set the formula to this one:

=INDIRECT( "B" & row() ) = 55

And because we actually want two conditions and our requirement is not as simple as that, I tried the following:

=AND( INDIRECT( "B" & row() ) >= 50, INDIRECT( "B" & row() ) <= 60 )

Which means set the background to green if the value is between 50 and 60 (inclusive). And that did not work. But the following works:

=AND( INDIRECT( "B" & "2" ) >= 50, INDIRECT( "B" & "2" ) <= 60 )

Could anyone tell me why does the latter work but the former that calls row() doesn't?

It seems row() only doesn't work if it's used with combination of AND() and INDIRECT() as demonstrated below:

=AND( ROW() = 2 ) -> this one works
=INDIRECT( "B" & row() ) = 55 -> this one works too
=AND( INDIRECT( "B" & row() ) ) = 55 -> doesn't work :((

Could someone explain this why? Is this a bug in Excel 2010? And how do I solve this problem?

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

The behavior you faced looks like a bug, here are some forum links, where people describe exactly the same problem: link1,link2.

In one of those topics you can read that if you enclose your CF formula with ISERROR, it returns (for unknown reason) True. Same formula entered in a cell works perfectly fine.

The easiest solution in your case is to replace:

=AND(condition1,condition2)

With equivalent:

=(condition1)*(condition2)

The other workaround shown in one of the linked threads is to replace:

INDIRECT("B" & row())

With its equivalent:

INDEX($B:$B,ROW())

Both solutions work fine in conditional formatting rule.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...