Thread: return reference if in date period and 'yes' Thanks:  1 Post #5344782 (1) Likes: 0

1. Re: return reference if in date period and 'yes' Originally Posted by mattbnorris You clever clever man! Thank you so much!
You're very welcome. Thanks for the kind words.   Reply With Quote

2. Re: return reference if in date period and 'yes' Originally Posted by Peter_SSs 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

 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

Hi Peter,

Currently revisiting this query I now want to create this into one big list of with different date periods after each other in the list. So i've started with a nested if statement where i'm using your formula (as quoted above) based on how many records i've found in a date range. For example...

 IDs Q1 (Jan-Mar) Q2 (Apr-Jun) Q3 (Jul-Sep) Q4 (Oct-Dec) #111 Jan 3 2 1 0 #333 Mar #444 Jan #777 Apr #999 Jun

Okay under the quarter headings i'm counting the amount of times it finds a record in that time period and then to get the list i'm using a nested IF function with the formula previously provided

=IF(\$E\$4+2=ROW(A1),IF(\$E\$4+\$F\$4+2=ROW(A1),[...continued...],Aggregate_Data_Formula(01/04/19 - 30/06/19)),Aggregate_Data_Formula(01/01/19 - 31/03/19))

Dragging this down works until it gets to the changing point - so in my example above it returns the first 3 values that are part of Q1 but then won't return further values. The issue is due to the ROW formula in previously provided formula:

=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))),"")

If i'm nesting the formulas in IF statements as I propose then the ROW part at the end of formula above needs to also include the reference to the Q1 count; as in it would need to start at ROWS(A\$5:A5) in order to provide the right IDs.

I know that's long winded, apologies if you don't understand.

Thanks,
Matt  Reply With Quote

3. Re: return reference if in date period and 'yes'

Are those month abbreviations in column B ..

- Actual Dates with cells formatted as mmm, or
- Text Values  Reply With Quote

4. Re: return reference if in date period and 'yes' Originally Posted by Peter_SSs Are those month abbreviations in column B ..

- Actual Dates with cells formatted as mmm, or
- Text Values

It was just a quick notation for the table on here, in my actual sheet I have actual dates. But its fine, I managed to figure my problem out actually...

=ROW(INDIRECT(E\$4+F\$4+2&":A"ROW(A1))

and dragging this down allows me to get the required outcome.

Thanks  Reply With Quote

User Tag List

Tags for this Thread

#444, 01/01/19, assigned, date, return  Posting Permissions

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