Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I am having some issues with using INDIRECT to apply conditional formatting.

I have column G which carries a percentage. I want to conditionally format column H based on the percentage being greater than 0 and less than 100.

I have attempted the following without success:

=AND(INDIRECT("G"&ROW())<100%,INDIRECT("G"&ROW())>0%)

This will return the correct value in a cell (true) and fail with conditional formatting

=IF(INDIRECT("G"&ROW())<100%,IF(INDIRECT("G"&ROW()>0%,TRUE))

This will return the correct value in a cell (true) and fail with conditional formatting

=IF($G2<100%,IF($G2>0%,TRUE))

I saw this in another solution. The $ is supposed to force a reevaluation on each row. This did not work either.

Additionally, I've tried both 100% and 1 in the formulas without success.

Any suggestions?

share|improve this question
    
Are you sure that it is failing with conditional formatting, or just that there is no format set (you have to set that manually with conditional rules that you create) –  soandos Nov 29 '11 at 22:03
    
I've set the format. I have it working using the above formulas for 100% and 0%. The issue only appears on a conditional formatting statement that uses multiple indirects. –  Mervyn Nov 29 '11 at 22:15
add comment

1 Answer 1

You don't need the If in Conditional Formatting. Just use:

AND($G2<100%,$G2>0%)

It took me a while to stop wanting to include If in my Conditional Formats, but the CF itself is testing for the condition you specify, so no If needed.

Also the "$" is for a fixed reference. It forces the formula to always refer to column G, which would be relevant if your CF was for multiple columns. The lack of a "$" in front of the 2 makes that part of the cell address relative, which means the CF will always check the row it's in. Relative referencing is important to understand, so if my explanation doesn't make sense, you might want to google it.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.