janarthenan
New Member
- Joined
- Jun 11, 2016
- Messages
- 22
Hi All
I currently have the below UDF for my spreadsheet.
It looks at two columns and counts the highest date, if the leavers column is empty and the date entered is less than the DateToCheck
I would like to add 2 more variables to this UDF if possible.
1. I would the UDF to count based on Designation which is on a seperate level table.
2. I would like the UDF to ignore row if the StartDate is after DateToCheck.
Also currently the Previous and the Current columns are next to each other, THe UDF does not work if the columns are not next to each other.
I would like to keep the Previous columns together.
<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
<tbody>
</tbody>
I currently have the below UDF for my spreadsheet.
It looks at two columns and counts the highest date, if the leavers column is empty and the date entered is less than the DateToCheck
I would like to add 2 more variables to this UDF if possible.
1. I would the UDF to count based on Designation which is on a seperate level table.
2. I would like the UDF to ignore row if the StartDate is after DateToCheck.
Also currently the Previous and the Current columns are next to each other, THe UDF does not work if the columns are not next to each other.
I would like to keep the Previous columns together.
Code:
Function Count_Training(RngToLookIn As Range, DateToCheck As Date, rngIgnore As Range) As Long Dim aRangeToLookIn As Variant, aIgnore As Variant
Dim EarlierDate As Date
Dim i As Long, j As Long
aRangeToLookIn = RngToLookIn.Value
aIgnore = Intersect(RngToLookIn.EntireRow, rngIgnore.EntireColumn).Value
EarlierDate = DateAdd("m", -12, DateToCheck)
For i = 1 To UBound(aRangeToLookIn, 1)
If Len(aIgnore(i, 1)) = 0 Then
For j = 1 To UBound(aRangeToLookIn, 2)
If aRangeToLookIn(i, j) <= DateToCheck Then
If aRangeToLookIn(i, j) >= EarlierDate Then
Count_Training = Count_Training + 1
Exit For
End If
End If
Next j
End If
Next i
End Function
A | B | C | D | E | F | G | H | I | J | |
---|---|---|---|---|---|---|---|---|---|---|
1 | Trainee | Start Date | Designation | Infection control | Infection control - Previous | Safeguarding Adults | Safeguarding Adults - Previous | Basic Life Support | Basic Life Support - Previous | Leaver |
2 | A | 03/01/2003 | HSCW | Bank | ||||||
3 | B | 01/03/2003 | MANAGER | 20/12/18 | 20/12/18 | 15/12/18 | 20/12/18 | 23/12/18 | ||
4 | C | 01/09/2003 | DOMESTIC | 01/10/18 | 02/04/15 | 02/04/15 | ||||
5 | D | 01/08/2005 | SN | 01/07/18 | 07/01/17 | 07/01/17 | 20/05/17 | 07/01/17 | Leaver | |
6 | E | 30/04/2006 | SN | 29/12/18 | 29/12/18 | 29/12/18 | 21/10/18 | 21/10/18 | ||
7 | F | 04/06/2007 | SHSC | 16/08/18 | 16/08/18 | 16/08/18 | 21/05/17 | 16/08/18 | 11/10/18 | |
8 | G | 06/11/2008 | SN | 30/07/18 | 30/07/18 | 30/07/18 | 30/07/18 | |||
9 | H | 01/02/2009 | SN | 15/072018 | 15/072018 | 15/07/18 | 19/09/17 | 20/04/17 | ||
10 | I | 13/02/2010 | SHSC | 03/07/17 | 03/07/17 | 23/05/17 | 07/07/17 | 01/11/16 | Leaver | |
11 | J | 20/06/2010 | SN | 25/05/18 | 25/05/18 | 25/05/18 | 25/05/18 | 25/05/18 | ||
12 | K | 28/06/2010 | SHSC | 15/08/18 | 15/08/18 | 15/08/18 | 04/11/17 | 03/12/17 | ||
13 | L | 07/10/2010 | D.MANAGER | 31/10/18 | 31/10/18 | 31/10/18 | 31/10/18 | 31/10/18 | 12/10/18 | |
14 | M | 26/10/2010 | SHSC | 15/08/18 | 15/08/18 | 15/08/18 | 15/08/18 | 23/11/16 | ||
15 | N | 09/05/2011 | SHSC | 14/12/18 | 14/12/18 | 31/10/18 | 18/10/18 | 09/12/16 | 11/10/18 | |
16 | O | 31/10/2011 | ADMIN | 08/09/15 | 08/09/15 | 08/09/15 | Leaver | |||
17 | ||||||||||
18 | ||||||||||
19 | Infection Control | Safeguarding Adults | Basic Life Support | Level 1 | Level 2 | Level 3 | ||||
20 | Training Stats | 10 | 10 | 7 | HSCW | DOMESTIC | MANAGER | |||
21 | SN | ADMIN | D. MANAGER | |||||||
22 | SHSCW |
<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Stat. & Mand. Training
Worksheet Formulas
<thead> </thead><tbody> </tbody> |
<tbody>
</tbody>