Conditional Formatting in spreadsheet
I am trying to create some data checks in a spreadsheet to automate our document cross-reference checks. I have the source data linked to information throughout our document via references and not source links, but when I try to apply conditional formatting to a row to say "if these cells do not equal this cell" they all turn red regardless of if they are the same number or not. Any thoughts?
-
Hello and welcome to the Community, Tessa!
Great question. Let me see if I can assist here. I've done a conditional formatting like this before, where you change formatting based on either value of the corresponding cell, or a single cell. In this case it sounds like you ware wanting to look at the value in a single cell? If so, here's a simple setup:
Conditional Format
I am referencing the value in cell J10 with some absolute references.
Spreadsheet
The J10 value here is 100 so anything in cell ranges B10 thru B26 that doesn't equal 100 shows up red.
Is this what you are looking to do, or have I missed the mark? Let me know either way and I'll be happy to help however I can. Thanks for your contributions and have a great rest of your day!
0
Hi Mike,
Thank you so much for your reply! That was a great example, and I just tried to apply it, and am still getting false "errors." B8 is the referenced cell, and I am trying to apply the rule to the three values in red. As you can see, one doesn't match, but the other two do, yet all three are still turning red. I'm not sure where it's going wrong. Thank you!
0
Hmm... thanks for sharing that image. Very helpful.
One suggestion internally was to change the When from "Formula is true" to "Value equals".
0
Still no luck unfortunately. I tried when "value equals" and "does not equal" with no logical results.
0
0
Hello again, Tessa!
Still looking at this today. One thing that came up was perhaps the formula setting for As Displayed vs Full Precision. If the formulas are calculating at full precision there are additional digits in the cell that aren't being displayed which likely don't match the cell you're comparing to. If that is the case, the formula being switched to As Displayed should make this work the way you expect.
0
Or also try the option for Value does not equal with =$B$8. We've tested that and finding it works more as expected.
0
Hi Mike,
Unfortunately making those changes still hasn't solved the issue. I can just create a formula column as an alternative.
Thank you!
0サインインしてコメントを残してください。
コメント
8件のコメント