Index

[kizzie37]

Can someone assist with this

I have a sheet that counts the number of entries in the data sheet

=SUMPRODUCT(--('Data Sheet'!$B$3:$B$1000=C3),--('Data Sheet'!$G$3:$G$1000>TODAY()-WEEKDAY(TODAY())),--('Data Sheet'!$G$3:$G$1000<=TODAY()-WEEKDAY(TODAY())+7))

Then by smallest date (due this week)

=IF(ROWS($L$8:L8)<=Y$5,SMALL(IF(('Data Sheet'!$G$3:$G$1000>TODAY()-WEEKDAY(TODAY()))*('Data Sheet'!$G$3:$G$1000<=TODAY()-WEEKDAY(TODAY())+7)*('Data Sheet'!$B$3:$B$1000=$C$3),'Data Sheet'!$G$3:$G$1000),ROWS($Y$8:$Y8)),"")

This line is indexing the first ref of the returned date and not the date that matches with the data sheet G3??so indexing the wrong task, the task is associated with a different project "C3"

=IF(N($Y8),INDEX('Data Sheet'!C$3:C$1000,SMALL(IF('Data Sheet'!$G$3:$G$1000=$Y8,ROW('Data Sheet'!$C$3:$C$1000)-ROW('Data Sheet'!$G$3)+1),COUNTIF($Y8:$Y$8,$Y8))),"")

Book1
	V	W	X	Y
3	8 Tasks
4
5				3
6	TASKS DUE THIS WEEK
7	Task /Activity			Due Date
8	Distribute Memo			1/20/2020
9	Create Presentation Resources			1/21/2020
10	Reach out for Recruiting Templates			1/22/2020
Dashboard

Cell Formulas
Range	Formula
V3	V3	=VLOOKUP(Dashboard!$C$3,'Projects Summary'!$B$2:$G$59,2,FALSE) &" Tasks"
Y5	Y5	=SUMPRODUCT(--('Data Sheet'!$B$3:$B$1000=C3),--('Data Sheet'!$G$3:$G$1000>TODAY()-WEEKDAY(TODAY())),--('Data Sheet'!$G$3:$G$1000<=TODAY()-WEEKDAY(TODAY())+7))
V8	V8	{=IF(N($Y8),INDEX('Data Sheet'!C$3:C$1000,SMALL(IF('Data Sheet'!$G$3:$G$1000=$Y8,ROW('Data Sheet'!$C$3:$C$1000)-ROW('Data Sheet'!$C$3)+1),COUNTIF($Y8:$Y$8,$Y8))),"")}
Y8:Y10	Y8	{=IF(ROWS($L$8:L8)<=Y$5,SMALL(IF(('Data Sheet'!$G$3:$G$1000>TODAY()-WEEKDAY(TODAY()))*('Data Sheet'!$G$3:$G$1000<=TODAY()-WEEKDAY(TODAY())+7)*('Data Sheet'!$B$3:$B$1000=$C$3),'Data Sheet'!$G$3:$G$1000),ROWS($Y$8:$Y8)),"")}
V9:V10	V9	{=IF(N($Y9),INDEX('Data Sheet'!C$3:C$1000,SMALL(IF('Data Sheet'!$G$3:$G$1000=$Y9,ROW('Data Sheet'!$C$3:$C$1000)-ROW('Data Sheet'!$C$3)+1),COUNTIF($Y$8:$Y9,$Y9))),"")}
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

 

[jasonb75]

This line is indexing the first ref of the returned date and not the date that matches with the data sheet G3??so indexing the wrong task, the task is associated with a different project "C3"

=IF(N($Y8),INDEX('Data Sheet'!C$3:C$1000,SMALL(IF('Data Sheet'!$G$3:$G$1000=$Y8,ROW('Data Sheet'!$C$3:$C$1000)-ROW('Data Sheet'!$G$3)+1),COUNTIF($Y8:$Y$8,$Y8))),"")

For the formula to pick up the rows that match C3, you would need that as a criteria in the formula, the same as you have in the Y8:Y10 formula.

=IF(N($Y8),INDEX('Data Sheet'!C$3:C$1000,SMALL(IF(('Data Sheet'!$G$3:$G$1000=$Y8)*('Data Sheet'!$B$3:$B$1000=$C$3),ROW('Data Sheet'!$C$3:$C$1000)-ROW('Data Sheet'!$G$3)+1),COUNTIF($Y8:$Y$8,$Y8))),"")

I think that should do it.