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:
<tbody>
</tbody>
<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!!!
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!!!
Last edited: