Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: return reference if in date period and 'yes'

  1. #1
    New Member
    Join Date
    Sep 2019
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Red face return reference if in date period and 'yes'

    So I have a table with a list of loads of different references with dates assigned to them and also 'yes' or 'no' assigned to them. I need to return the reference numbers if the date is in a specific period and they have 'yes' assigned.

    Example
    Date Ref Status Jan-Mar 2019 & Yes
    01/01/19 #111 Yes #111
    23/01/19 #444 Yes #444
    25/02/19 #333 No #222
    01/01/19 #222 Yes #555
    07/03/19 #555 Yes

  2. #2
    Board Regular
    Join Date
    Jun 2005
    Location
    London
    Posts
    7,751
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    1 Thread(s)

    Default Re: return reference if in date period and 'yes'

    =IF(AND(A2>=DATE(2019,1,1),A2<=DATE(2019,3,31),C2="Yes"),B2,"")
    Neil

  3. #3
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,985
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    21 Thread(s)

    Default Re: return reference if in date period and 'yes'

    Similar concept to this one I helped you with.

    Date Yes

    ABCDEFGH
    1DateRefStatus Start Month1 List
    21/01/2019#111Yes End Month3 #111
    323/01/2019#444Yes StatusYes #444
    425/02/2019#333No #222
    51/01/2019#222Yes #555
    67/03/2019#555Yes

    Spreadsheet Formulas
    CellFormula
    H2=IFERROR(INDEX(B$2:B$6,AGGREGATE(15,6,(ROW(B$2:B$6)-ROW(B$2)+1)/((MONTH(A$2:A$6)>=F$1)*(MONTH(A$2:A$6)<=F$2)*(C$2:C$6="Yes")),ROWS(H$2:H2))),"")


    Excel tables to the web >> Excel Jeanie HTML 4
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  4. #4
    New Member
    Join Date
    Sep 2019
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: return reference if in date period and 'yes'

    Hi Peter, thanks for the help on this once again. Does this method work even with columns A, B and C in a different sheet or workbook?

  5. #5
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,985
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    21 Thread(s)

    Default Re: return reference if in date period and 'yes'

    Quote Originally Posted by mattbnorris View Post
    Hi Peter, thanks for the help on this once again. Does this method work even with columns A, B and C in a different sheet or workbook?
    Give it a try?
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  6. #6
    New Member
    Join Date
    Sep 2019
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Unhappy Re: return reference if in date period and 'yes'

    Quote Originally Posted by Peter_SSs View Post
    Give it a try?

    Hi peter, I have tried, I've also tried putting the information on the same sheet and its not working. I think it's something to do with the equation inside the aggregate equation? Not sure

  7. #7
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,985
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    21 Thread(s)

    Default Re: return reference if in date period and 'yes'

    If you set up a fresh sheet exactly like the one in post 3 does it work?

    Simply saying "its not working" gives us nothing to go on, especially when you can see the posted example is working.
    When things don't work, you need to explain in what way they don't work (gives #REF ! error, Returns "x" instead of "y", crashes Excel etc) and give an example or two of sample data that it failed for.
    Last edited by Peter_SSs; Sep 20th, 2019 at 04:00 AM.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  8. #8
    New Member
    Join Date
    Sep 2019
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Lightbulb Re: return reference if in date period and 'yes'

    Quote Originally Posted by Peter_SSs View Post
    Give it a try?
    Hello again! I got something to work however now I want multiple status conditions to be picked up
    Example

    Date Ref Status Jan-Mar 19 "Active" & "Reactive" & "Yes"
    15/01/19 #111 Yes #111
    14/02/18 #222 No #333
    27/03/19 #333 Active #444
    30/01/19 #444 Reactive
    12/01/19 #555 No


    I tried this but it doesn't seem to be working, is there a limit on the conditions I can have in there?

    =INDEX(B$2:B$6,AGGREGATE(15,6,(ROW(B$2:B$6)-ROW(B$2)+1)/((MONTH(A$2:A$6)>=1)*(MONTH(A$2:A$6)<=3)*(C$2:C$6="Reactive")*(C$2:C$6="Active")*(C$2:C$6="Yes")*(YEAR(A$2:A$6)=2019)),ROWS(H$2:H2)))


    This returns a #NUM ! error
    Last edited by mattbnorris; Sep 20th, 2019 at 04:09 AM. Reason: Because I need to explain what happens when things don't work

  9. #9
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,985
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    21 Thread(s)

    Default Re: return reference if in date period and 'yes'

    Quote Originally Posted by mattbnorris View Post
    I tried this but it doesn't seem to be working, is there a limit on the conditions I can have in there?

    =INDEX(B$2:B$6,AGGREGATE(15,6,(ROW(B$2:B$6)-ROW(B$2)+1)/((MONTH(A$2:A$6)>=1)*(MONTH(A$2:A$6)<=3)*(C$2:C$6="Reactive")*(C$2:C$6="Active")*(C$2:C$6="Yes")*(YEAR(A$2:A$6)=2019)),ROWS(H$2:H2)))
    There is no practical limit to the number of conditions, however when multiplying the conditions like that it acts like AND. Of course a value in C2:C6 cannot be "Reactive" AND "Active" AND "Yes" at the same time.

    Here is one way to do it.

    Date Yes (2)

    ABCDEFGH
    1DateRefStatus List
    215/01/2019#111Yes #111
    314/02/2018#222No #333
    427/03/2019#333Active #444
    530/01/2019#444Reactive
    612/01/2019#555No
    7

    Spreadsheet Formulas
    CellFormula
    H2=IFERROR(INDEX(B$2:B$6,AGGREGATE(15,6,(ROW(B$2:B$6)-ROW(B$2)+1)/((MONTH(A$2:A$6)>=1)*(MONTH(A$2:A$6)<=3)*(ISNUMBER(MATCH(C$2:C$6,{"Reactive","Active","Yes"},0)))*(YEAR(A$2:A$6)=2019)),ROWS(H$2:H2))),"")


    Excel tables to the web >> Excel Jeanie HTML 4
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  10. #10
    New Member
    Join Date
    Sep 2019
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: return reference if in date period and 'yes'

    Quote Originally Posted by Peter_SSs View Post
    There is no practical limit to the number of conditions, however when multiplying the conditions like that it acts like AND. Of course a value in C2:C6 cannot be "Reactive" AND "Active" AND "Yes" at the same time.

    Here is one way to do it.

    Date Yes (2)

    A B C D E F G H
    1 Date Ref Status List
    2 15/01/2019 #111 Yes #111
    3 14/02/2018 #222 No #333
    4 27/03/2019 #333 Active #444
    5 30/01/2019 #444 Reactive
    6 12/01/2019 #555 No
    7

    Spreadsheet Formulas
    Cell Formula
    H2 =IFERROR(INDEX(B$2:B$6,AGGREGATE(15,6,(ROW(B$2:B$6)-ROW(B$2)+1)/((MONTH(A$2:A$6)>=1)*(MONTH(A$2:A$6)<=3)*(ISNUMBER(MATCH(C$2:C$6,{"Reactive","Active","Yes"},0)))*(YEAR(A$2:A$6)=2019)),ROWS(H$2:H2))),"")


    Excel tables to the web >> Excel Jeanie HTML 4

    You clever clever man! Thank you so much!

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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