Index cell value based on date conditions

[jeff106]

	A	B	C	D	E	F
1	Store #	Store Name	Date Due	Completed By	Owner
2	1234	Abby	5/12	John	Albert
3	5467	Cothes	8/12	Pete	Albert
4	3452	Main	9/3	Steve	Jeff
5	3553	Ramsy	10/8		Albert
6	5324	Corner	11/8		Jeff

<tbody>
</tbody>

Hi All,
I have a tracker set up which tracks completion and due dates for specific things for several stores. On a summary page, I'd like to use the INDEX function to show a summary of stores showing the Store # and Store Name that meet certain criteria.

So basically I would have a summary table that shows all of Albert's restaurants that have due dates that are past due based on today(). In that table, I want the store number and store names to be listed.

On a separate summary table I want to list store # and names of restaurants that don't have a name filled in on the "Completed By" column.

And then I would replicate these same tables for all of Jeff's stores.

I know that I will also have to integrate COUNTIF into the formula to accurately display the list of restaurants and not to repeat the same store name or number.

Any help on this?
Thank you!

 

[Aladin Akyurek]

Sheet1 (data)

Row\Col	A​	B​	C​	D​	E​
1​	Store #	Store Name	Date Due	Completed By	Owner
2​	1234	Abby	5/12/2017​	John	Albert
3​	5467	Cothes	8/12/2017​	Pete	Albert
4​	3452	Main	9/3/2017​	Steve	Jeff
5​	3553	Ramsy	10/8/2017​		Albert
6​	5324	Corner	11/8/2017​		Jeff

<tbody>
</tbody>

Sheet2 (name-based due date processing)

Row\Col	A​	B​	C​
1​	Albert
2​	1​
3​	Idx	Store #	Store Name
4​	4​	3553​	Ramsy
5​

<tbody>
</tbody>

In A2 just enter:

=COUNTIFS(Sheet1!$C$2:$C$6,">"&TODAY(),Sheet1!$E$2:$E$6,$A$1)

In A4 control+shift+enter, not just enter, and copy down:

=IF(ROWS($A$4:A4)>$A$2,"",SMALL(IF(Sheet1!$C$2:$C$6>TODAY(),ROW(Sheet1!$C$2:$C$6)-ROW(Sheet1!$C$2)+1),ROWS($A$4:A4)))

In B4 just enter, copy across, and down:

=IF($A4="","",INDEX(Sheet1!$A$2:$E$6,$A4,MATCH(B$3,Sheet1!$A$1:$E$1,0)))

Sheet3 (no completed by processing)

Row\Col	A​	B​	C​
1​	Completed By Not Filled
2​	2​
3​	Idx	Store #	Store Name
4​	1​	1234​	Abby
5​	2​	5467​	Cothes
6​

<tbody>
</tbody>

In A2 just enter:

=COUNTIFS(Sheet1!$D$2:$D$6,"")

In A4 control+shift+enter, not just enter, and copy down:

=IF(ROWS($A$4:A4)>$A$2,"",SMALL(IF(1-(Sheet1!$D$2:$D$6=""),ROW(Sheet1!$D$2:$D$6)-ROW(Sheet1!$D$2)+1),ROWS($A$4:A4)))

In B4 just enter, copy across, and down:

=IF($A4="","",INDEX(Sheet1!$A$2:$E$6,$A4,MATCH(B$3,Sheet1!$A$1:$E$1,0)))

I leave "replication" to you.

 

[jeff106]

Sheet1 (data)

Row\Col	A​	B​	C​	D​	E​
1​	Store #	Store Name	Date Due	Completed By	Owner
2​	1234	Abby	5/12/2017​	John	Albert
3​	5467	Cothes	8/12/2017​	Pete	Albert
4​	3452	Main	9/3/2017​	Steve	Jeff
5​	3553	Ramsy	10/8/2017​		Albert
6​	5324	Corner	11/8/2017​		Jeff

<tbody>
</tbody>

Sheet2 (name-based due date processing)

Row\Col	A​	B​	C​
1​	Albert
2​	1​
3​	Idx	Store #	Store Name
4​	4​	3553​	Ramsy
5​

<tbody>
</tbody>

In A2 just enter:

=COUNTIFS(Sheet1!$C$2:$C$6,">"&TODAY(),Sheet1!$E$2:$E$6,$A$1)

In A4 control+shift+enter, not just enter, and copy down:

=IF(ROWS($A$4:A4)>$A$2,"",SMALL(IF(Sheet1!$C$2:$C$6>TODAY(),ROW(Sheet1!$C$2:$C$6)-ROW(Sheet1!$C$2)+1),ROWS($A$4:A4)))

In B4 just enter, copy across, and down:

=IF($A4="","",INDEX(Sheet1!$A$2:$E$6,$A4,MATCH(B$3,Sheet1!$A$1:$E$1,0)))

Sheet3 (no completed by processing)

Row\Col	A​	B​	C​
1​	Completed By Not Filled
2​	2​
3​	Idx	Store #	Store Name
4​	1​	1234​	Abby
5​	2​	5467​	Cothes
6​

<tbody>
</tbody>

In A2 just enter:

=COUNTIFS(Sheet1!$D$2:$D$6,"")

In A4 control+shift+enter, not just enter, and copy down:

=IF(ROWS($A$4:A4)>$A$2,"",SMALL(IF(1-(Sheet1!$D$2:$D$6=""),ROW(Sheet1!$D$2:$D$6)-ROW(Sheet1!$D$2)+1),ROWS($A$4:A4)))

In B4 just enter, copy across, and down:

=IF($A4="","",INDEX(Sheet1!$A$2:$E$6,$A4,MATCH(B$3,Sheet1!$A$1:$E$1,0)))

I leave "replication" to you.

Thank you for your help! This worked great! Quick question. For the first summary table - I want to show list of stores under "Albert" occurring after today but within 30 days of today's date. So if there are 100 stores for Albert - only the ones with dates in a 30 day window will list. I tried using an AND statement in the IF formula, but I can't get it to work.

=IF(ROWS($A$25:A25)>$A$24,"",SMALL(IF((AND(Tracker!$Q$8:$Q$900>TODAY(),Tracker!$Q$8:$Q$900<E4+30)),ROW(Tracker!$Q$8:$Q$900)-ROW(Tracker!$Q$8)+1),ROWS($A$25:A25)))

E4 Is set as today() on the sheet.

 

[Aladin Akyurek]

Thank you for your help! This worked great! Quick question. For the first summary table - I want to show list of stores under "Albert" occurring after today but within 30 days of today's date. So if there are 100 stores for Albert - only the ones with dates in a 30 day window will list. I tried using an AND statement in the IF formula, but I can't get it to work.

=IF(ROWS($A$25:A25)>$A$24,"",SMALL(IF((AND(Tracker!$Q$8:$Q$900>TODAY(),Tracker!$Q$8:$Q$900<e4+30)),row(tracker!$q$8:$q$900)-row(tracker!$q$8)+1),rows($a$25:a25)))

E4 Is set as today() on the sheet.

Sheet2 (name-based due date processing)

Row\Col	A​	B​	C​
1​	Albert	9/29/17​	30​
2​	1​
3​	Idx	Store #	Store Name
4​	4​	3553​	Ramsy
5​
6​

In A1 just enter:

=COUNTIFS(Sheet1!$C$2:$C$6,">"&$B$1,Sheet1!$C$2:$C$6,"<="&$B$1+$C$1,Sheet1!$E$2:$E$6,$A$1)

In A4 control+shift+enter and copy down:

=IF(ROWS($A$4:A4)>$A$2,"",SMALL(IF(Sheet1!$C$2:$C$6>$B$1,IF(Sheet1!$C$2:$C$6<=$B$1+$C$1,ROW(Sheet1!$C$2:$C$6)-ROW(Sheet1!$C$2)+1)),ROWS($A$4:A4)))

In B4 just enter, copy across, and down:

=IF($A4="","",INDEX(Sheet1!$A$2:$E$6,$A4,MATCH(B$3,Sheet1!$A$1:$E$1,0)))

Within 3 days is implemented as between today and today+30. Modify if it's otherwise.



</e4+30)),row(tracker!$q$8:$q$900)-row(tracker!$q$8)+1),rows($a$25:a25)))

 

[jeff106]

Sheet2 (name-based due date processing)

Row\Col	A​	B​	C​
1​	Albert	9/29/17​	30​
2​	1​
3​	Idx	Store #	Store Name
4​	4​	3553​	Ramsy
5​
6​

<tbody>
</tbody>

In A1 just enter:

=COUNTIFS(Sheet1!$C$2:$C$6,">"&$B$1,Sheet1!$C$2:$C$6,"<="&$B$1+$C$1,Sheet1!$E$2:$E$6,$A$1)

In A4 control+shift+enter and copy down:

=IF(ROWS($A$4:A4)>$A$2,"",SMALL(IF(Sheet1!$C$2:$C$6>$B$1,IF(Sheet1!$C$2:$C$6<=$B$1+$C$1,ROW(Sheet1!$C$2:$C$6)-ROW(Sheet1!$C$2)+1)),ROWS($A$4:A4)))

In B4 just enter, copy across, and down:

=IF($A4="","",INDEX(Sheet1!$A$2:$E$6,$A4,MATCH(B$3,Sheet1!$A$1:$E$1,0)))

Within 3 days is implemented as between today and today+30. Modify if it's otherwise.

Aladin - Thank you for your help! These formulas are working great. One issue I am running into, the first formula in A1 works great to calculate the number of times "Albert" has a date that is greater than today but less than today +30 days.

The second and third formulas in A4 and B4 work great to call the line number and index the store number and store name. HOWEVER, the formula in A4 is calling line number of the very first date that meets the criteria (greater than today but less than today+30). So if there is another store owner who has a date further up the list that meets the criteria - it is calling that line number regardless of whether it belongs to Albert or not. Is there another IF statement I need in formula #2 in order to match store owner with the other logical tests?

UPDATE: NEVERMIND! Figured it out! Just added another nested IF statement to match the Owner name in formula 2.

Thank you Aladin for your help!!

 

[Aladin Akyurek]

[…]

UPDATE: NEVERMIND! Figured it out! Just added another nested IF statement to match the Owner name in formula 2.

Indeed the formula of A4 must include an IF testing for A1 that house Albert.

Thank you Aladin for your help!!

You are welcome.