• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Really simple question

LadyBlack

New Member
Hello all


I just can't seem to get my head round this. I want to do something really simple, and I just can't get it straight.


I am trying to compile a list of names. This is the situation :- We have sent out an invite. We are receiving back replies. I want the spreadsheet to show green if they respond "Yes", blue if they respond "No" and purple if they respond "Unsure".


My conditional formatting formula is very simple, the formula is if the cell value = "Yes", then turn it green. However, as soon as I try to apply it to other cells, it all goes wrong. I have tried selecting all the cells, and then applying the formula, but some of the cells turned green, then as I clicked into the column which controlled the conditions, the white cells then turned green as I clicked down them, even though the cell value was empty. If I then turned the response to "No", the cells remained green.


I have read the article on this website about conditional formatting, which is when I tried selecting all my cells first, but obviously I'm still doing something wrong.


At the moment, I seem to be saying that if any of the cells contain the value "Yes" then turn the cells green. But equally, I'm also saying if any of them equal "No", then turn the cells blue. Which may be confusing Excel somewhat.


Please can anyone help?
 
LadyBlack

Assuming your range starts at A1


Your 3 Rules should be

=A1="Yes"

=A1="No"

=A1="Unsure"

with the appropriate formats set
 
Hello Hui


Yes, I understand that's what it should be for each individual cell. What I'm trying to do (sorry, haven't explained properly) is apply the same formula to a range of cells. I don't want to have to change each line of my spreadsheet. Please can you advise on how to do that?


Many thanks
 
LadyBlack

When applying Conditional Formating (CF), select the whole area you want CF's to apply to

eg: A1:A1000

and then apply the rules as listed above based on the first cell of the range

Excel will automatically adjust the CF formulas in the rest

Make sure you enter =A1="Yes" insteaed of =$A$1="Yes"


If the cells you are applying are using a formula to apply the Yes, No, Unsure values, you can apply a CF to one cell and then copy it
 
Hello Hui


Sorry for not replying. I've just come back to this, and although I have failed to copy the formula to all the cells first off, I can still copy the first cell, and then Paste Special to all the other cells, and that works.


I can't seem to apply the changing colour to all the cells, but never mind, this will still point out who's coming and who isn't.


Many thanks
 
Lady Black

You select all the cells before going into CF

When you go into conditional formating, apply the formula to the first cell only

eg:

If A1 is the first cell

Your 3 Rules should be

=A1="Yes"

=A1="No"

=A1="Unsure

Adjust for the appropriate First Cell


When it is applied, Excel will adjust each cell relatively, so in A2 it will apply an adjusted formula depending on the Cells "A2" relationship to A1

Your 3 Rules in A2 will be

=A2="Yes"

=A2="No"

=A2="Unsure

etc


Can you post your file somewhere if that doesn't help ?
 
Back
Top