need help with conditional formatting

[dzugan]

I have a column A1:A10 that sums up all "yes" entries in cell A12 using:

COUNTIF(A1:A10,"=yes")

What I would like Excel to do is actually delete these entries if the sum of all "yes" exceeds 8. I had the idea that I could use conditional formatting to change the text color of each "yes" to white if the sum is > 8, and this would in effect erase them because you wouldn't be able to see them (note that it doesn't matter to me that A12 still shows a number). I tried using the following formula:

=AND(A1="yes",A12>8)

It works, but only for cell A1. I tried both dragging the formula across and entering the formula as

=AND(A1:A10="yes",A12>8)

What am I doing wrong? Also, would there be a way to make Excel actually delete all "yes" if there were more than 8? I would want it to delete only the "yes", not any other entries.

[David A Coop]

Hi,

Just change the A12 to $A$12 in your conditional formatting code.

Please remember, your request is clearer if you attach a sample workbook.
- Click on Go Advanced and click on the Paper Clip.
When you reply please make it clear WHO you are responding to by mentioning their name.
If we have been of assistance, please let us know. A little thanks goes a long way.
- Please click on the *Add Reputation button at the bottom of helpful responses.
Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

[David A Coop]

Also, would there be a way to make Excel actually delete all "yes" if there were more than 8? I would want it to delete only the "yes", not any other entries.

dzugan

I forgot to respond to this bit.

I think you would have to resort to macros to accomplish this! How comfortable are you with adding a macro?

If you want to avoid macros, and do it manually, the best way would be to use the autofilter feature, filter on the column with Yes and delete the rows.

Regards,

David

[Qaisir]

See Picture attach
=IF(A12>8,"True")
solution.jpg

[dzugan]

Ah, stupid me! Thank you. BTW, I did attach the file.