+ Reply to Thread
Results 1 to 8 of 8

Return only values that's include true

  1. #1
    Registered User
    Join Date
    06-04-2022
    Location
    Iraq
    MS-Off Ver
    2019
    Posts
    4

    Return only values that's include true

    Hello guys,
    So i've two sheet ( Main , Summary )

    In Main sheet i've O column that's have True , False and blanks like :

    O5 =True
    O6 = False
    O7 = " " ( Means Blank )
    O8 = False
    O9 = " " ( Means Blank )
    O10 = " " ( Means Blank )
    O11 = " " ( Means Blank )
    O12 = True

    so i just want in Summary sheet to get only "True" in column O in main sheet and if true give me the J cells

    so what i used in Summary sheet in A5 is : =IF(Main!O5,Main!J5,"")

    so what i get was :

    A5 = give me what i need
    A6 = " " ( Means Blank )
    A7 = #VALUE!
    A8 = " " ( Means Blank )
    A9 = #VALUE!
    A10 = #VALUE!
    A11 = #VALUE!
    A12 = give me what i need


    So i just need only to get ( give me what i need ) row under row without ( #VALUE! and " " ( Means Blank ) ) how can i get that ? and get rid of ( #VALUE! and " " ( Means Blank ) ) ?

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,090

    Re: Return only values that's include true

    Use:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Or, don't put " " for blank … that is a single space. Leave the cell empty or, if it is from a formula, use "" … that is two double quote characters with NO space character between them.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Registered User
    Join Date
    06-04-2022
    Location
    Iraq
    MS-Off Ver
    2019
    Posts
    4

    Re: Return only values that's include true

    Oh !! It's worked all the #Value! has gone and still only blanks so any solution to remove these blanks ?

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Return only values that's include true

    Try this in A5 drag copied down:

    =IFERROR(INDEX(Main!J$5:J$12,AGGREGATE(15,6,ROW($1:$500)/(Main!O$5:O$12=TRUE),ROW(1:1))),"")
    Attached Files Attached Files
    Last edited by AliGW; 06-05-2022 at 03:50 AM. Reason: Workbook attached.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  5. #5
    Registered User
    Join Date
    06-04-2022
    Location
    Iraq
    MS-Off Ver
    2019
    Posts
    4

    Re: Return only values that's include true

    it's gave me blanks when i put the formula in the A5 and when drag the formula all rows become blank

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Return only values that's include true

    See the workbook attached above. It works fine.

    AliGW on MS365 Beta Channel (Windows 11) 64 bit

    A
    B
    5
    10
    =IFERROR(INDEX(Main!J$5:J$12,AGGREGATE(15,6,ROW($1:$500)/(Main!$O$5:$O$12=TRUE),ROW(1:1))),"")
    6
    17
    Sheet: Summary

    If you can't make it work, sttach a workbook showing the problem. It will be either user error or a difference with the real dataset.
    Attached Files Attached Files
    Last edited by AliGW; 06-05-2022 at 03:43 AM.

  7. #7
    Registered User
    Join Date
    06-04-2022
    Location
    Iraq
    MS-Off Ver
    2019
    Posts
    4

    Re: Return only values that's include true

    Wow i'm very thankful it's worked when i changed J$5:J$12 and made it J$5:J$100

    Thank you too much

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Return only values that's include true

    You will need to change the O range to match.

    Glad to have helped.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

    Also, if you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Lookup four values and return True if one of them found.
    By globalsourcing in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-01-2020, 12:26 PM
  2. Look up if dates in range and return all true values
    By Bensley in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-05-2019, 05:22 AM
  3. Replies: 7
    Last Post: 12-06-2018, 12:50 PM
  4. Replies: 3
    Last Post: 08-16-2018, 06:07 AM
  5. Search for values and return true or false value
    By awd2013 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 01-12-2015, 02:01 PM
  6. If two cells contain any values then return TRUE
    By djarcadian in forum Excel General
    Replies: 2
    Last Post: 11-13-2014, 03:36 PM
  7. [SOLVED] if multiple values same/others different, return true
    By Jshendel in forum Excel General
    Replies: 7
    Last Post: 08-21-2006, 01:15 PM

Tags for this Thread

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