Ignore blank cells on matched values with multiple results

[edgillenw]

Excel 2013 Windows 10 Surface Pro 3

I am creating a training database where each employee has a training sheet and then I have a summary sheet that looks at each individual sheet, determines if the training is past due, and returns the name of the necessary training course.

Example Training Sheet:

Column A	Column B	Column C	Column D
Training Course	Frequency (yrs)	Training Date	Training Due Date
First Aid	1	1/1/2015	1/1/2016
Electrical Safety	1	1/1/2015	1/1/2016
House Keeping	1	1/1/2015	1/1/2016
Demonstrated experience	One Time	1/1/2015	---

<tbody>
</tbody>

I have the training due date set to auto calculate and use conditional formatting to alert me of past due, upcoming in 30 days and upcoming in 90 days. <now()cell value="" <now()+30 <now()=60 However, as the number of employees grows it is too difficult to look at each individual page each month. Therefore, I want a summary sheet. Summary Sheet: I found a formula on a previous post that can be used to return the value I want and allows for multiple results. Cell G8 referenced below is where my formula resides on the sheet. =IF(COUNTIF($D$5:$D$120,NOW()) <=ROWS($G$8:G8), INDEX($A$5:$A$120,SMALL(IF($D$5:$D$120<=NOW(), ROW($D$5:$D$120)-ROW($D$5)+1),ROWS($G$8:G8))),"")</now()=60 </now()cell>

<tbody>
</tbody>

My issue is that in the rows where column D is blank, the formula still returns the text written in column A. I tried changing the beginning of my formula to include IF($D$5:$D$120="","",(COUNTIF... however that does not change anything. I believe the issue lies in the COUNTIF statement.

Specific question: How can I use the above formula while ignoring blank cells in column D? Help on this will be greatly appreciated!!!

 

[AhoyNC]

Try:
This is an array formula and must be entered with CTRL-SHIFT-ENTER

=IF(COUNTIF($D$5:$D$120,[COLOR=#ff0000]"<"&[/COLOR]NOW()) [COLOR=#ff0000]>=[/COLOR]ROWS($G$8:G8),INDEX($A$5:$A$120,SMALL(IF($D$5:$D$120<=NOW(),[COLOR=#ff0000]IF($D$5:$D$120<>""[/COLOR],ROW($D$5:$D$120)-ROW($D$5)+1)[COLOR=#ff0000])[/COLOR],ROWS($G$8:G8))),"")

 

[edgillenw]

Thank you very much!!! Your edits to the formula seem to be solving my problem. AMAZING!

 

[edgillenw]

I have another question. I am trying to extend this model to include due in 30 days and due in 60 days as well. I naively assumed I could just change the NOW() to NOW()+30 and NOW()+60 but that doesn't seem to work. Any ideas?

 

[AhoyNC]

A couple of options:
Both are array formulas.

=IF(COUNT(IF($D$5:$D$120>=NOW()-[COLOR=#ff0000]30[/COLOR],IF($D$5:$D$120<=NOW(),IF($D$5:$D$120<>"",ROW($D$5:$D$120)-ROW($D$5)+1))))>=ROWS($G$8:G8),INDEX($A$5:$A$120,SMALL(IF($D$5:$D$120>=NOW()-[COLOR=#ff0000]30[/COLOR],IF($D$5:$D$120<=NOW(),IF($D$5:$D$120<>"",ROW($D$5:$D$120)-ROW($D$5)+1))),ROWS($G$8:G8))),"")

Change the 30 to 60 for 60 days.

A short version would be to use the IFERROR function. This would run a little slower, but with only 115 rows you probably won't see a difference.

=IFERROR(INDEX($A$5:$A$120,SMALL(IF($D$5:$D$120>=NOW()-30,IF($D$5:$D$120<=NOW(),IF($D$5:$D$120<>"",ROW($D$5:$D$120)-ROW($D$5)+1))),ROWS($G$8:G8))),"")

 

[edgillenw]

I tried both and they both work with the same result, which was unfortunately not the result I want. Both of these options provided me the items that were past due but not the items that will be due in the next 30 days or next 60 days.

 

[edgillenw]

I kept messing around with the code of the short version and finally figured it out. Still couldn't figure out the long version.

=IFERROR(INDEX($A$5:$A$120,SMALL(IF($D$5:$D$120[COLOR=#ff0000]<[/COLOR]NOW()[COLOR=#ff0000]+[/COLOR]30,IF($D$5:$D$120[COLOR=#ff0000]>[/COLOR]=NOW(),IF($D$5:$D$120<>"",ROW($D$5:$D$120)-ROW($D$5)+1))),ROWS($G$8:G8))),"")

 

[AhoyNC]

Glad you got it to work. Sorry, I misunderstood what you were looking for on the 30 & 60 days.

 

[edgillenw]

Thank you for all your help! I would have never gotten this completed with it.