Index cell value based on date conditions

J

jeff106

New Member
Joined
Dec 2, 2016
Messages
21
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Store #[/TD]
[TD]Store Name[/TD]
[TD]Date Due[/TD]
[TD]Completed By
[/TD]
[TD]Owner[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1234[/TD]
[TD]Abby[/TD]
[TD]5/12[/TD]
[TD]John[/TD]
[TD]Albert[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]5467[/TD]
[TD]Cothes[/TD]
[TD]8/12[/TD]
[TD]Pete[/TD]
[TD]Albert[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]3452[/TD]
[TD]Main[/TD]
[TD]9/3[/TD]
[TD]Steve[/TD]
[TD]Jeff[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]3553[/TD]
[TD]Ramsy[/TD]
[TD]10/8[/TD]
[TD][/TD]
[TD]Albert[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]5324[/TD]
[TD]Corner[/TD]
[TD]11/8[/TD]
[TD][/TD]
[TD]Jeff[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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!
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Sort by date Sort by votes
Sheet1 (data)

[TABLE="class: grid"]
<tbody>[TR]
[TD]Row\Col[/TD]
[TD]
A​
[/TD]
[TD]
B​
[/TD]
[TD]
C​
[/TD]
[TD]
D​
[/TD]
[TD]
E​
[/TD]
[/TR]
[TR]
[TD]
1​
[/TD]
[TD] Store #[/TD]
[TD] Store Name[/TD]
[TD] Date Due[/TD]
[TD] Completed By[/TD]
[TD] Owner[/TD]
[/TR]
[TR]
[TD]
2​
[/TD]
[TD] 1234[/TD]
[TD] Abby[/TD]
[TD]
5/12/2017​
[/TD]
[TD] John[/TD]
[TD] Albert[/TD]
[/TR]
[TR]
[TD]
3​
[/TD]
[TD] 5467[/TD]
[TD] Cothes[/TD]
[TD]
8/12/2017​
[/TD]
[TD] Pete[/TD]
[TD] Albert[/TD]
[/TR]
[TR]
[TD]
4​
[/TD]
[TD] 3452[/TD]
[TD] Main[/TD]
[TD]
9/3/2017​
[/TD]
[TD] Steve[/TD]
[TD] Jeff[/TD]
[/TR]
[TR]
[TD]
5​
[/TD]
[TD] 3553[/TD]
[TD] Ramsy[/TD]
[TD]
10/8/2017​
[/TD]
[TD][/TD]
[TD] Albert[/TD]
[/TR]
[TR]
[TD]
6​
[/TD]
[TD] 5324[/TD]
[TD] Corner[/TD]
[TD]
11/8/2017​
[/TD]
[TD][/TD]
[TD] Jeff[/TD]
[/TR]
</tbody>[/TABLE]

Sheet2 (name-based due date processing)

[TABLE="class: grid"]
<tbody>[TR]
[TD]Row\Col[/TD]
[TD]
A​
[/TD]
[TD]
B​
[/TD]
[TD]
C​
[/TD]
[/TR]
[TR]
[TD]
1​
[/TD]
[TD]Albert[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
2​
[/TD]
[TD]
1​
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
3​
[/TD]
[TD]Idx[/TD]
[TD]Store #[/TD]
[TD]Store Name[/TD]
[/TR]
[TR]
[TD]
4​
[/TD]
[TD]
4​
[/TD]
[TD]
3553​
[/TD]
[TD]Ramsy[/TD]
[/TR]
[TR]
[TD]
5​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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)

[TABLE="class: grid"]
<tbody>[TR]
[TD]Row\Col[/TD]
[TD]
A​
[/TD]
[TD]
B​
[/TD]
[TD]
C​
[/TD]
[/TR]
[TR]
[TD]
1​
[/TD]
[TD]Completed By Not Filled[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
2​
[/TD]
[TD]
2​
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
3​
[/TD]
[TD]Idx[/TD]
[TD]Store #[/TD]
[TD]Store Name[/TD]
[/TR]
[TR]
[TD]
4​
[/TD]
[TD]
1​
[/TD]
[TD]
1234​
[/TD]
[TD]Abby[/TD]
[/TR]
[TR]
[TD]
5​
[/TD]
[TD]
2​
[/TD]
[TD]
5467​
[/TD]
[TD]Cothes[/TD]
[/TR]
[TR]
[TD]
6​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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.
 
Last edited:
Upvote 0
Aladin Akyurek said:
Sheet1 (data)

[TABLE="class: grid"]
<tbody>[TR]
[TD]Row\Col
[/TD]
[TD]
A​
[/TD]
[TD]
B​
[/TD]
[TD]
C​
[/TD]
[TD]
D​
[/TD]
[TD]
E​
[/TD]
[/TR]
[TR]
[TD]
1​
[/TD]
[TD] Store #
[/TD]
[TD] Store Name
[/TD]
[TD] Date Due
[/TD]
[TD] Completed By
[/TD]
[TD] Owner
[/TD]
[/TR]
[TR]
[TD]
2​
[/TD]
[TD] 1234
[/TD]
[TD] Abby
[/TD]
[TD]
5/12/2017​
[/TD]
[TD] John
[/TD]
[TD] Albert
[/TD]
[/TR]
[TR]
[TD]
3​
[/TD]
[TD] 5467
[/TD]
[TD] Cothes
[/TD]
[TD]
8/12/2017​
[/TD]
[TD] Pete
[/TD]
[TD] Albert
[/TD]
[/TR]
[TR]
[TD]
4​
[/TD]
[TD] 3452
[/TD]
[TD] Main
[/TD]
[TD]
9/3/2017​
[/TD]
[TD] Steve
[/TD]
[TD] Jeff
[/TD]
[/TR]
[TR]
[TD]
5​
[/TD]
[TD] 3553
[/TD]
[TD] Ramsy
[/TD]
[TD]
10/8/2017​
[/TD]
[TD][/TD]
[TD] Albert
[/TD]
[/TR]
[TR]
[TD]
6​
[/TD]
[TD] 5324
[/TD]
[TD] Corner
[/TD]
[TD]
11/8/2017​
[/TD]
[TD][/TD]
[TD] Jeff
[/TD]
[/TR]
</tbody>[/TABLE]

Sheet2 (name-based due date processing)

[TABLE="class: grid"]
<tbody>[TR]
[TD]Row\Col
[/TD]
[TD]
A​
[/TD]
[TD]
B​
[/TD]
[TD]
C​
[/TD]
[/TR]
[TR]
[TD]
1​
[/TD]
[TD]Albert
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
2​
[/TD]
[TD]
1​
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
3​
[/TD]
[TD]Idx
[/TD]
[TD]Store #
[/TD]
[TD]Store Name
[/TD]
[/TR]
[TR]
[TD]
4​
[/TD]
[TD]
4​
[/TD]
[TD]
3553​
[/TD]
[TD]Ramsy
[/TD]
[/TR]
[TR]
[TD]
5​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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)

[TABLE="class: grid"]
<tbody>[TR]
[TD]Row\Col
[/TD]
[TD]
A​
[/TD]
[TD]
B​
[/TD]
[TD]
C​
[/TD]
[/TR]
[TR]
[TD]
1​
[/TD]
[TD]Completed By Not Filled
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
2​
[/TD]
[TD]
2​
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
3​
[/TD]
[TD]Idx
[/TD]
[TD]Store #
[/TD]
[TD]Store Name
[/TD]
[/TR]
[TR]
[TD]
4​
[/TD]
[TD]
1​
[/TD]
[TD]
1234​
[/TD]
[TD]Abby
[/TD]
[/TR]
[TR]
[TD]
5​
[/TD]
[TD]
2​
[/TD]
[TD]
5467​
[/TD]
[TD]Cothes
[/TD]
[/TR]
[TR]
[TD]
6​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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.
Click to expand...

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.
 
Upvote 0
jeff106 said:
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.
Click to expand...

Sheet2 (name-based due date processing)

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]
A​
[/td][td]
B​
[/td][td]
C​
[/td][/tr][tr][td]
1​
[/td][td]Albert[/td][td]
9/29/17​
[/td][td]
30​
[/td][/tr]
[tr][td]
2​
[/td][td]
1​
[/td][td][/td][td][/td][/tr]
[tr][td]
3​
[/td][td]Idx[/td][td]Store #[/td][td]Store Name[/td][/tr]
[tr][td]
4​
[/td][td]
4​
[/td][td]
3553​
[/td][td]Ramsy[/td][/tr]
[tr][td]
5​
[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]
6​
[/td][td][/td][td][/td][td][/td][/tr]
[/table]

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)))
 
Upvote 0
Aladin Akyurek said:
Sheet2 (name-based due date processing)

[TABLE="class: grid"]
<tbody>[TR]
[TD]Row\Col
[/TD]
[TD]
A​
[/TD]
[TD]
B​
[/TD]
[TD]
C​
[/TD]
[/TR]
[TR]
[TD]
1​
[/TD]
[TD]Albert
[/TD]
[TD]
9/29/17​
[/TD]
[TD]
30​
[/TD]
[/TR]
[TR]
[TD]
2​
[/TD]
[TD]
1​
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
3​
[/TD]
[TD]Idx
[/TD]
[TD]Store #
[/TD]
[TD]Store Name
[/TD]
[/TR]
[TR]
[TD]
4​
[/TD]
[TD]
4​
[/TD]
[TD]
3553​
[/TD]
[TD]Ramsy
[/TD]
[/TR]
[TR]
[TD]
5​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
6​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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.
Click to expand...



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!!
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Solved
create a report summary page by group
Replies
4
Views
175
PippaThePointer
P
J
  • Question Question
Calculation based on value in table based on date range
Replies
0
Views
227
jsand
J
Kellie220
  • Solved
Formula for calculating different dates/ VBA
Replies
3
Views
661
awoohaw
awoohaw
k3yn0t3
  • Solved
INDEX(MATCH()) pulling wrong row data & Macro to duplicate sheets with formulas
Replies
4
Views
544
Alex Blakenburg
A
J
  • Question Question
Conditional formatting based on value of another cell
Replies
3
Views
656
etaf
etaf

Forum statistics

Threads
1,223,240
Messages
6,170,951
Members
452,368
Latest member
jayp2104

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top