Welcome Guest ( Log In | Join )

Discover the best free computer help!
Learn more about Geeks to Go by taking the tour. Want to ask a question, reply to a topic, or remove all advertising? It's easy, fast and free. Join today!
Spyware, virus, trojan, fake security or privacy alerts? Please start with our malware cleaning guide.
     
 
 Reply to this topicStart new topic
Create an IF formula, Want Excel to recognise if text=YES to insert Yes in different Cell
kill_hXc
post Oct 28 2008, 08:46 AM
Post #1


Member
***
Posts: 101
From: London, England
OS: Windows XP (PC) Windows Vista (Laptop)
Hi,

The title says it all really.

I need to know how to make one cell say either 'Yes' if 3 other cells say yes or 'No' if they dont.

i.e.

If A1, B1 and C1 says 'Yes' (selected from a drop down list) I want D1 to say 'Yes' and be coloured green.

If A1, B1 and C1 says 'No' (selected from drop down list) I want D1 to say 'No' and be coloured Red.

Any help will be greatly appreciated.

Thanks in advance,

Tom.
Go to the top of the page
 
+Quote Post
dsenette
post Oct 28 2008, 09:20 AM
Post #2


Ruler of Omicron Persei 8
Group Icon
Posts: 20,895
From: kn-ARGH!-xville Tenn-ARGH!-see
OS: 3.1, 95, 98SE, xp, 2000, NT4, 2003 Standard Edition, linux (various flavors)
what you would want to do to get the color option is place conditional formatting on the D column that says if the cell is equal to "Yes" make it green and if it's "no" make it red

the other part is a bit funkier...

this formula
CODE
=IF(AND(A1="yes",B1="yes",C1="yes"),"YES","NO")
will make the cells in column D (assuming that's where you put this formula) say "YES" if and ONLY if A1,B1, and C1 all say yes....any other condition (i.e. yes no yes, no no yes, no no no, etc..) will result in a NO...it's probably possible to much around with that and see what you could do...there are ways to do a nested if etc... that might get it...i'll play around with it
Go to the top of the page
 
+Quote Post
kill_hXc
post Oct 28 2008, 10:06 AM
Post #3


Member
***
Posts: 101
From: London, England
OS: Windows XP (PC) Windows Vista (Laptop)
Fantastic!

Thankyou so much.

I was messing around with things like but not quite that!

You can close this thread now if you want.
Go to the top of the page
 
+Quote Post
Jonesey
post Oct 28 2008, 10:50 AM
Post #4


Member
***
Posts: 192
From: Bristol, England
OS: Windows XP Professional, SP3
That's definitely the way I'd do it desenette.

2 things to bear in mind:

1 - The 'Yes's' and 'No's' in the formula aren't case senstivie, so any combination of YES, Yes yes etc will work - you don't have to be picky about spelling.

2 - Conditional formatting is VERY resource intensive and If you have a large number of rows, might well lock up the spreadsheet.


Just my 2 cents.
Go to the top of the page
 
+Quote Post
dsenette
post Oct 28 2008, 11:30 AM
Post #5


Ruler of Omicron Persei 8
Group Icon
Posts: 20,895
From: kn-ARGH!-xville Tenn-ARGH!-see
OS: 3.1, 95, 98SE, xp, 2000, NT4, 2003 Standard Edition, linux (various flavors)
QUOTE
2 - Conditional formatting is VERY resource intensive and If you have a large number of rows, might well lock up the spreadsheet.
indeed...but it's the only way i know of to change the color of a cell based on criteria..

Edit:also...i attempted a nested if statement that basically said "if a,b, and c are yes make d yes....but if a,b, and c are no make d no, else make d no regardless" but it didn't work .
Go to the top of the page
 
+Quote Post
Jonesey
post Oct 28 2008, 01:17 PM
Post #6


Member
***
Posts: 192
From: Bristol, England
OS: Windows XP Professional, SP3
QUOTE (dsenette @ Oct 28 2008, 05:30 PM) *
QUOTE
2 - Conditional formatting is VERY resource intensive and If you have a large number of rows, might well lock up the spreadsheet.
indeed...but it's the only way i know of to change the color of a cell based on criteria..

Edit:also...i attempted a nested if statement that basically said "if a,b, and c are yes make d yes....but if a,b, and c are no make d no, else make d no regardless" but it didn't work .




Yep - the only other way would be via a macro which obviously has to be run and wouldn't work on the fly.
Go to the top of the page
 
+Quote Post
 

Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 

Collapse

> Similar Topics

    Topic Title Replies / Views Topic Information
No New Posts   3 / 858 4th March 2008 - 09:42 PM
thebumswife started - last by Ztruker
No New Posts   1 / 115 23rd September 2008 - 05:10 PM
NotAQuitter started - last by Neil Jones
No New Posts 7 / 347 20th October 2008 - 10:00 AM
SkyLogic started - last by SRX660
No New Posts   1 / 106 16th December 2008 - 10:06 AM
gailerl started - last by happyrock

RSS Time is now: 9th January 2009 - 02:38 PM
Advertisements do not imply our endorsement of that product or service. The forum is run by volunteers who donate their time and expertise. We make every attempt to ensure that the help and advice posted is accurate and will not cause harm to your computer. However, we do not guarantee that they are accurate and they are to be used at your own risk.
Powered By IP.Board © 2009  IPS, Inc.