+ Reply to Thread
Results 1 to 9 of 9

compare cells in column below and if all = Yes then return Yes, Else Return No

  1. #1
    Registered User
    Join Date
    05-16-2013
    Location
    Californa
    MS-Off Ver
    Excel 2003
    Posts
    4

    compare cells in column below and if all = Yes then return Yes, Else Return No

    i am trying to use a cell to validate date below it.

    if i have finished all the child tasks below that relate to the overall completion of the parent task, then I want it to say Yes

    If all the child tasks are not complete, then i want it to say No.



    Example;

    I used this IF statement and all i get (no matter what is in the cells B11-B14) is #VALUE!
    if i put all yes' or a combination of Yes or No ... nothing changes

    =IF(B11:B14,"Yes","No")

    cells B11 through B14 have data validation on them so that the only thing you can enter in the cells is a Yes or a No

  2. #2
    DaveDeV
    Guest

    Re: compare cells in column below and if all = Yes then return Yes, Else Return No

    One thing you could try...

    Add a column for the sub-tasks and populate with a "1" if task is complete or "0" if task incomplete.
    Use COUNT to determine the number of sub-tasks and SUM of the 1/0 column to determine how many sub-tasks are complete
    if the COUNT of all sub-tasks = the sum of the 1/0 column then all sub-tasks are complete

    Alternatively, use COUNTIF (where the "completed" column = "Yes") and compare the result with the COUNT of sub-tasks

    I hope that helps

    Dave

  3. #3
    Registered User
    Join Date
    05-16-2013
    Location
    Californa
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: compare cells in column below and if all = Yes then return Yes, Else Return No

    Thanks Dave,

    I can make Numbers work, but that does not work for the users of the spreadsheet.

    this is for the use of many users and i would really like to make it user freindly if the child taks is done the Yes is the response that makes sense ... a 1 or a 0 (although logical to me) does not work for them.

  4. #4
    DaveDeV
    Guest

    Re: compare cells in column below and if all = Yes then return Yes, Else Return No

    Hi Zino,

    If that's the case then use the COUNTIF to count the number of "Yes" values and compare that to the COUNT of sub-tasks - if they're equal then all sub-tasks have been completed.

    If you want to attach a sample, I'll knock something together for you.

    Dave

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: compare cells in column below and if all = Yes then return Yes, Else Return No

    perhaps...
    =IF(countif(B11:B14,"Yes")=counta(B11:B14),"Yes","No")
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  6. #6
    Registered User
    Join Date
    05-16-2013
    Location
    Californa
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: compare cells in column below and if all = Yes then return Yes, Else Return No

    Thanks again Dave.

    Here is a small sample of what it looks like

    Column A Column B


    Documents Required (All Sites) #VALUE! <---- should say No at this point
    A&E Plans/ Zoning Drawings No
    LOA (or the equivalent) from Property Owner Yes
    LOA (or the equivalent) from Tower Owner Yes
    RF Coverage Maps (With and Without Site) Yes

  7. #7
    DaveDeV
    Guest

    Re: compare cells in column below and if all = Yes then return Yes, Else Return No

    That should work...

    Dave

  8. #8
    Registered User
    Join Date
    05-16-2013
    Location
    Californa
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: compare cells in column below and if all = Yes then return Yes, Else Return No

    Quote Originally Posted by FDibbins View Post
    perhaps...
    =IF(countif(B11:B14,"Yes")=counta(B11:B14),"Yes","No")
    This works ... Thanks

  9. #9
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: compare cells in column below and if all = Yes then return Yes, Else Return No

    Happy to help. If this answered your question, please take a moment to mark the thread as "solved" - it helps keep things neet and tidy lol, and consider adding the reputation to those that helped (see points 2 & 3 below)

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1