+ Reply to Thread
Results 1 to 19 of 19

Turn cell a5 to TRUE if cells b5,c5,d5 equal yes

  1. 01-17-2016, 09:37 AM #1
    surk
    surk is offline
    Registered User
    Join Date
    08-13-2015
    Location
    Portsmouth, UK
    MS-Off Ver
    2010, 2013
    Posts
    44

    Turn cell a5 to TRUE if cells b5,c5,d5 equal yes

    Hi there

    Please can someone advise the best way of having a cell automatically turn true or false depending on other cells.

    I need cell A5 to contain TRUE, FALSE or a nil value

    If cells B5:D5 contain NA or Yes, I need cell A5 to state TRUE

    If any of cells B5:D5 contain NO, then I need cell A5 to contain FALSE

    Many thanks
    Reply With QuoteRegister To Reply
  2. 01-17-2016, 09:50 AM #2
    Sam Capricci
    Sam Capricci is offline
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.84 for Mac MS 365
    Posts
    8,499

    Re: Turn cell a5 to TRUE if cells b5,c5,d5 equal yes

    how about =IF(COUNTIF(B5:D2,"na")+COUNTIF(B5:D5,TRUE)>0,TRUE,FALSE)
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci
    Reply With QuoteRegister To Reply
  3. 01-17-2016, 10:08 AM #3
    surk
    surk is offline
    Registered User
    Join Date
    08-13-2015
    Location
    Portsmouth, UK
    MS-Off Ver
    2010, 2013
    Posts
    44

    Re: Turn cell a5 to TRUE if cells b5,c5,d5 equal yes

    hi there - unfortunately that does not quite work
    The default value needs to be blank
    If YES or NA on all cells, then the value needs to be true
    If any cells contain NO then regardless of the other values it needs to be FALSE
    Reply With QuoteRegister To Reply
  4. 01-17-2016, 10:20 AM #4
    surk
    surk is offline
    Registered User
    Join Date
    08-13-2015
    Location
    Portsmouth, UK
    MS-Off Ver
    2010, 2013
    Posts
    44

    Re: Turn cell a5 to TRUE if cells b5,c5,d5 equal yes

    please see example of the combinations and the answer would need a formula to come back with - many thanks
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  5. 01-17-2016, 11:53 AM #5
    newdoverman
    newdoverman is offline
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Turn cell a5 to TRUE if cells b5,c5,d5 equal yes

    This formula returns the results that you have indicated for the criteria in your example. I'm not sure if there are more combinations possible that you haven't shown.
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W
    Reply With QuoteRegister To Reply
  6. 01-17-2016, 02:13 PM #6
    surk
    surk is offline
    Registered User
    Join Date
    08-13-2015
    Location
    Portsmouth, UK
    MS-Off Ver
    2010, 2013
    Posts
    44

    Re: Turn cell a5 to TRUE if cells b5,c5,d5 equal yes

    Many thanks - that all appears to work!
    Reply With QuoteRegister To Reply
  7. 01-17-2016, 03:14 PM #7
    newdoverman
    newdoverman is offline
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Turn cell a5 to TRUE if cells b5,c5,d5 equal yes

    You're welcome. Thank you for the feedback.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
    Reply With QuoteRegister To Reply
  8. 01-18-2016, 06:44 AM #8
    surk
    surk is offline
    Registered User
    Join Date
    08-13-2015
    Location
    Portsmouth, UK
    MS-Off Ver
    2010, 2013
    Posts
    44

    Re: Turn cell a5 to TRUE if cells b5,c5,d5 equal yes

    Hi there - Sorry I need a slight adjustment to this, The TRUE/FALSE needs to go in D4, butt heer are more criteria from D9:d14, and simply changing the cell codes does not seem to work.
    Reply With QuoteRegister To Reply
  9. 01-18-2016, 10:09 AM #9
    newdoverman
    newdoverman is offline
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Turn cell a5 to TRUE if cells b5,c5,d5 equal yes

    Can you upload another workbook with a mock-up with the new requirements? Indicate the anticipated answer where you want the answers to be.
    Reply With QuoteRegister To Reply
  10. 01-18-2016, 02:44 PM #10
    surk
    surk is offline
    Registered User
    Join Date
    08-13-2015
    Location
    Portsmouth, UK
    MS-Off Ver
    2010, 2013
    Posts
    44

    Re: Turn cell a5 to TRUE if cells b5,c5,d5 equal yes

    If possible rather than TRUE/FALSE, can I have PASS/FAIL??

    Thanks so much in advance, it is much appreciated

    And a nice learning curve!
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  11. 01-18-2016, 03:35 PM #11
    newdoverman
    newdoverman is offline
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Turn cell a5 to TRUE if cells b5,c5,d5 equal yes

    I take it that you want to evaluate the columns from row 9 to row14 and have the evaluation in row 4 from D4:J4
    Enter this in D4 and fill across
    Formula: copy to clipboard 
    Please Login or Register  to view this content.

    C
    D
    E
    F
    G
    H
    I
    J
    4
    		 Pass Fail Fail Pass Pass Fail
    5
    6
    7
    8
    9
    		 q1. yes Yes No Yes Yes - No
    10
    		 q2 yes Yes Yes NA Yes - no
    11
    		 q3 yes Yes Yes NA NA - no
    12
    		 q4 yes Yes No NA Yes - no
    13
    		 q5 yes Yes No NA YES - no
    14
    		 q6 yes No No NA YES - no
    Reply With QuoteRegister To Reply
  12. 01-18-2016, 03:52 PM #12
    surk
    surk is offline
    Registered User
    Join Date
    08-13-2015
    Location
    Portsmouth, UK
    MS-Off Ver
    2010, 2013
    Posts
    44

    Re: Turn cell a5 to TRUE if cells b5,c5,d5 equal yes

    Apologies, almost perfect, just one scenario I omitted. If any cells in D9:D14 contain a " - " then I need D5 to default to " - "
    Reply With QuoteRegister To Reply
  13. 01-18-2016, 03:54 PM #13
    surk
    surk is offline
    Registered User
    Join Date
    08-13-2015
    Location
    Portsmouth, UK
    MS-Off Ver
    2010, 2013
    Posts
    44

    Re: Turn cell a5 to TRUE if cells b5,c5,d5 equal yes

    ** DOES NOT WORK, AND CANNOT DELETE MY OWN POST! **

    Dont worry, just solved it I think ... =IF(COUNTIF(M9:M14,"-")>0,"",IF(COUNTIF(M9:M14,"Yes")+COUNTIF(M9:M14,"NA")=6,"Pass","Fail"))

    Quote Originally Posted by surk View Post
    Apologies, almost perfect, just one scenario I omitted. If any cells in D9:D14 contain a " - " then I need D5 to default to " - "
    Last edited by surk; 01-18-2016 at 04:00 PM.
    Reply With QuoteRegister To Reply
  14. 01-18-2016, 03:56 PM #14
    newdoverman
    newdoverman is offline
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Turn cell a5 to TRUE if cells b5,c5,d5 equal yes

    This will put the - in for columns with -
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  15. 01-18-2016, 04:21 PM #15
    surk
    surk is offline
    Registered User
    Join Date
    08-13-2015
    Location
    Portsmouth, UK
    MS-Off Ver
    2010, 2013
    Posts
    44

    Re: Turn cell a5 to TRUE if cells b5,c5,d5 equal yes

    I have uploaded an updated book version with your formulas in row 4, and what I need the answers to be in row 3

    Many thanks
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  16. 01-18-2016, 04:40 PM #16
    newdoverman
    newdoverman is offline
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Turn cell a5 to TRUE if cells b5,c5,d5 equal yes

    This might be "sneaking up" on the answer
    Formula: copy to clipboard 
    Please Login or Register  to view this content.

    D
    E
    F
    G
    H
    I
    J
    K
    L
    M
    N
    O
    P
    Q
    3
    Pass
    Fail
    Fail
    Pass
    Pass
    -
    Fail
    -
    Pass
    -
    Fail
    -
    -
    Fail
    4
    Pass
    Fail
    Fail
    Pass
    Pass
    -
    Fail
    -
    Pass
    -
    Fail
    -
    -
    Fail
    5
    6
    7
    8
    9
    		 yes Yes No Yes Yes - No - yes - no Yes yes no
    10
    		 yes Yes Yes NA Yes - no - na - yes - yes no
    11
    		 yes Yes Yes NA NA - no - na - yes - - -
    12
    		 yes Yes No NA Yes - no - na yes yes - - -
    13
    		 yes Yes No NA YES - no - na - yes - - -
    14
    		 yes No No NA YES - no - na - yes - NA -
    Reply With QuoteRegister To Reply
  17. 01-18-2016, 04:49 PM #17
    surk
    surk is offline
    Registered User
    Join Date
    08-13-2015
    Location
    Portsmouth, UK
    MS-Off Ver
    2010, 2013
    Posts
    44

    Re: Turn cell a5 to TRUE if cells b5,c5,d5 equal yes

    Brilliant, I think that solves all possibilities
    Reply With QuoteRegister To Reply
  18. 01-18-2016, 04:54 PM #18
    newdoverman
    newdoverman is offline
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Turn cell a5 to TRUE if cells b5,c5,d5 equal yes

    Thank you for the feedback.
    Reply With QuoteRegister To Reply
  19. 01-18-2016, 04:56 PM #19
    popipipo
    popipipo is offline
    Forum Expert
    Join Date
    12-11-2011
    Location
    Netherlands
    MS-Off Ver
    office 365
    Posts
    3,293

    Re: Turn cell a5 to TRUE if cells b5,c5,d5 equal yes 

    Please Login or Register  to view this content.
    How about this formula?
    Attached Files Attached Files
    Last edited by popipipo; 01-18-2016 at 05:21 PM. Reason: attachment
    Willem
    English is not my native language sorry for errors
    Please correct me if I'm completely wrong
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Accumulate time when true and when false turn it to zero.
    By pedrohern in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-03-2015, 05:02 AM
  2. Accumulate time when true and when false turn it to cero.
    By pedrohern in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-02-2015, 12:36 PM
  3. macro to open userform1 turn optionbox1 to true and click commandbutton1
    By Jenkins27 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-15-2015, 09:20 PM
  4. Conditional formatting if two value equal true
    By grantsmith in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-16-2013, 07:05 AM
  5. Check if two cells are equal and copy,paste cell if true
    By solomeros in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-29-2011, 01:22 AM
  6. Toggle Button set to true if cell is equal to text
    By ravergirl7216 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-29-2010, 02:03 PM
  7. True, False and equal
    By scousepete79 in forum Excel General
    Replies: 3
    Last Post: 08-26-2007, 04:06 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1