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:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Column A[/TD]
[TD]Column B[/TD]
[TD]Column C[/TD]
[TD]Column D[/TD]
[/TR]
[TR]
[TD]Training Course[/TD]
[TD]Frequency (yrs)[/TD]
[TD]Training Date[/TD]
[TD]Training Due Date[/TD]
[/TR]
[TR]
[TD]First Aid[/TD]
[TD]1[/TD]
[TD]1/1/2015[/TD]
[TD]1/1/2016[/TD]
[/TR]
[TR]
[TD]Electrical Safety[/TD]
[TD]1[/TD]
[TD]1/1/2015[/TD]
[TD]1/1/2016[/TD]
[/TR]
[TR]
[TD]House Keeping[/TD]
[TD]1[/TD]
[TD]1/1/2015[/TD]
[TD]1/1/2016[/TD]
[/TR]
[TR]
[TD]Demonstrated experience[/TD]
[TD]One Time[/TD]
[TD]1/1/2015[/TD]
[TD]---[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 661"]
<tbody>[TR]
[TD]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>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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.