Excel Filter: Conditional Formatting Problem
January 16, 2018 9:54 AM Subscribe
Something is going wonky with my conditional formatting in a simple (I think) Excel sheet. I want cells in column D to turn green if their value is less than the value in the column C. No problem, except when I paste values into column B (column C is a running total working off of info in B), the conditional formatting stops working and turns cells green for awhile after it should stop.
This doesn't happen when I type the values, only when the values are pasted. However, pasting should ideally be an option. I can't find an example of this problem elsewhere online.
Screenshot of my rule
Screenshot of what is happening in my sheet
Column A is a plain value
Column B is where I add values (hopefully by pasting!)
Column C is a formula that adds the new citation count to the last to create a running total (eg, C3=C2+B3)
Column D is a formula that squares the value in column A (eg D3=POWER(A3,2)
Let me know if you need more info. Thanks for your keen eyes and Excel expertise.
This doesn't happen when I type the values, only when the values are pasted. However, pasting should ideally be an option. I can't find an example of this problem elsewhere online.
Screenshot of my rule
Screenshot of what is happening in my sheet
Column A is a plain value
Column B is where I add values (hopefully by pasting!)
Column C is a formula that adds the new citation count to the last to create a running total (eg, C3=C2+B3)
Column D is a formula that squares the value in column A (eg D3=POWER(A3,2)
Let me know if you need more info. Thanks for your keen eyes and Excel expertise.
Does the problem keep happening if you paste special -> Values instead of just pasting? Conditional formatting will be pulled along with a regular paste if you're copying from another excel sheet.
posted by Uncle at 10:17 AM on January 16, 2018
posted by Uncle at 10:17 AM on January 16, 2018
Response by poster: Ah, I think Ausamor found something at the heart of it. When I updated the value in the rule to "=$C2" that seemed to fix the issue. I'm still a little unclear on what was going on since I don't think that accounts for the formatting stopping at a seemingly arbitrary place. However, your direction helped solve the issue, so thank you!
posted by LKWorking at 10:25 AM on January 16, 2018
posted by LKWorking at 10:25 AM on January 16, 2018
Best answer: I'm still a little unclear on what was going on since I don't think that accounts for the formatting stopping at a seemingly arbitrary place. However, your direction helped solve the issue, so thank you!
What is the value of cell C192? Is it greater than 4489 and less than or equal to 4624? Having the $ in front of 192 meant every cell in your conditional formatting range was comparing itself to C192. When you changed it to $C2, it means your top cell compared to C2, second cell down compared to C3, third cell down compared to C4, etc.
posted by noneuclidean at 10:48 AM on January 16, 2018 [2 favorites]
What is the value of cell C192? Is it greater than 4489 and less than or equal to 4624? Having the $ in front of 192 meant every cell in your conditional formatting range was comparing itself to C192. When you changed it to $C2, it means your top cell compared to C2, second cell down compared to C3, third cell down compared to C4, etc.
posted by noneuclidean at 10:48 AM on January 16, 2018 [2 favorites]
« Older How to handle my reactions to a 18yo niece while... | Is it normal for employers to offer a low-paid one... Newer »
This thread is closed to new comments.
posted by Ausamor at 9:57 AM on January 16, 2018 [2 favorites]