Index and Match formula to return value

[everblazing]

Hello

I would really appreciate any help. I have a roster. I am trying to find the number of people available to work on a specific date. I want to be able to type a desired date in sheet 2 and be able to get number of people available on that date. Merge and Centre on date and year raw.

below is the example of the Roster.

]

Excel 2012
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA
1	ROSTER					1/01/2015	2/01/2015	5/01/2015	6/01/2015	7/01/2015	8/01/2015	9/01/2015	12/01/2015	13/01/2015	14/01/2015	15/01/2015	16/01/2015	19/01/2015	20/01/2015	21/01/2015	22/01/2015	23/01/2015	26/01/2015	27/01/2015	28/01/2015	29/01/2015	30/01/2015
2						Jan-15
3						Thu	Fri	Mon	Tue	Wed	Thu	Fri	Mon	Tue	Wed	Thu	Fri	Mon	Tue	Wed	Thu	Fri	Mon	Tue	Wed	Thu	Fri
4	Staff	Colour	State	Queues	team Manager	01	02	05	06	07	08	09	12	13	14	15	16	19	20	21	22	23	26	27	28	29	30
5	MNO PQR			Team C	NEF GER
6	ABC DEF			Team A	QRS TUV	P		S					A	A	A	A	A	A	A	A	A	A	P
7	GHE IJK			Team B	WXY ZRE	P				C													P		A
_2015_2016

Cell Formulas
Range	Formula
G1	=F1+1
G3	=TEXT(G1,"ddd")
H1	=G1+3
H3	=TEXT(H1,"ddd")
I1	=H1+1
I3	=TEXT(I1,"ddd")
J1	=I1+1
J3	=TEXT(J1,"ddd")
K1	=J1+1
K3	=TEXT(K1,"ddd")
L1	=K1+1
L3	=TEXT(L1,"ddd")
M1	=L1+3
M3	=TEXT(M1,"ddd")
N1	=M1+1
N3	=TEXT(N1,"ddd")
O1	=N1+1
O3	=TEXT(O1,"ddd")
P1	=O1+1
P3	=TEXT(P1,"ddd")
Q1	=P1+1
Q3	=TEXT(Q1,"ddd")
R1	=Q1+3
R3	=TEXT(R1,"ddd")
S1	=R1+1
S3	=TEXT(S1,"ddd")
T1	=S1+1
T3	=TEXT(T1,"ddd")
U1	=T1+1
U3	=TEXT(U1,"ddd")
V1	=U1+1
V3	=TEXT(V1,"ddd")
W1	=V1+3
W3	=TEXT(W1,"ddd")
X1	=W1+1
X3	=TEXT(X1,"ddd")
Y1	=X1+1
Y3	=TEXT(Y1,"ddd")
Z1	=Y1+1
Z3	=TEXT(Z1,"ddd")
AA1	=Z1+1
AA3	=TEXT(AA1,"ddd")
F3	=TEXT(F1,"ddd")

[/

 

[everblazing]

And here is the results part

Excel 2012
	A	B	C	D	E	F	G	H	I
308	Headcount. Less NWD/M/CB					284	270	274	274
309	Scheduled & Unscheduled Leave					239	122	71	67
310	Total % Scheduled Leave					84%	44%	24%	23%
311	Total % Unscheduled leave					0%	1%	2%	1%
312	Total % Available to Work					16%	55%	74%	76%
313	Total Available to Work (count)					45	148	203	207
_2015_2016

Cell Formulas
Range	Formula
F308	=SUBTOTAL(3,$A$6:$A$289)-F301-F294-F303
G308	=SUBTOTAL(3,$A$6:$A$289)-G301-G294-G303
H308	=SUBTOTAL(3,$A$6:$A$289)-H301-H294-H303
I308	=SUBTOTAL(3,$A$6:$A$289)-I301-I294-I303

[/

 

[Fishboy]

For anyone interested this was resolved. A312 and A313 were made into named ranges called TotalPercent and TotalAvailable respectively. Once these were named the following code was applied to the workbook:

Sub RunReport()
' Defines variables
Dim Rng As Range, Rng2 As Range, Rng3 As Range, FindString As String, FindDate As Date


' Screen updating to reduce flicker
Application.ScreenUpdating = False
' Disable events
Application.EnableEvents = False


' Define LastRow as where the names end in column A of the Roster sheet based on the blank row above "Annual Leave - A"
LastRow = Sheets("Roster").Cells(Rows.Count, "A").End(xlUp).Row
Set Rng3 = Sheets("Roster").Range("A1:A" & LastRow).Find(What:="", After:=Range("A5"))
' Define LastCol as the last column of the Roster containing a date in row 1
LastCol = Sheets("Roster").Cells(1, Columns.Count).End(xlToLeft).Column
' Set Rng as the Queue section of the Roster sheet
Set Rng = Sheets("Roster").Range("D4:D" & Rng3.Row)


' If the date and queue are both filled in on the Report sheet then...
If Sheets("Report").Range("C6").Value <> "" And Sheets("Report").Range("C5").Value <> "" Then
    ' Set FindString as the desired queue
    FindString = Sheets("Report").Range("C6").Value
    ' Set FindDate as the desired date
    FindDate = Sheets("Report").Range("C5").Value
    ' If the selected date is on A sunday then...
    If Weekday(FindDate) = vbSunday Then
        ' Display error message
        MsgBox vbNewLine & "The selected date falls on a Sunday.  Please enter a valid date."
        ' Clear the date from C5
        Sheets("Report").Range("C5").ClearContents
        ' Select C5 ready for a new date to be entered
        Sheets("Report").Range("C5").Select
        ' Exit the sub
        Exit Sub
    End If
    ' Activate the Roster sheet
    Sheets("Roster").Activate
    If Application.WorksheetFunction.CountIf(Sheets("Roster").Range(Cells(1, 6), Cells(1, LastCol)), FindDate) = 0 Then
        ' Display error message
        MsgBox vbNewLine & "The selected date does not exist in the Roster.  Please enter a valid date."
        ' Reactivate Report sheet
        Sheets("Report").Activate
        ' Clear the date from C5
        Sheets("Report").Range("C5").ClearContents
        ' Select C5 ready for a new date to be entered
        Sheets("Report").Range("C5").Select
        ' Exit the sub
        Exit Sub
    End If
    ' For each cell containing a date in the first row of the Roster sheet
    For Each Cell In Sheets("Roster").Range(Cells(1, 6), Cells(1, LastCol))
        ' If the cell value matches the desired date from the Report sheet then...
        If Cell.Value = FindDate Then
            ' Set Rng2 as the address of that cell
            Set Rng2 = Cell
            ' Exit the For loop early
            Exit For
        End If
    ' Check next cell
    Next
    ' Filter the Queue on the Roster based on the selected Queue
    Rng.AutoFilter
    Rng.AutoFilter Field:=1, Criteria1:=FindString
    ' Update C7 with the value from row 313 of the corresponding coloumn on the Roster sheet
    Sheets("Report").Range("C7").Value = Sheets("Roster").Cells(Range("TotalAvailable").Row, Rng2.Column).Value
    ' Update C8 with the value from row 312 of the corresponding coloumn on the Roster sheet
    Sheets("Report").Range("C8").Value = Sheets("Roster").Cells(Range("TotalPercent").Row, Rng2.Column).Value
    ' Turn the filter back off on the Roster sheet
    Sheets("Roster").AutoFilterMode = False
    ' Go back to the Report sheet
    Sheets("Report").Activate
    
' Re-enable events
Application.EnableEvents = True
' Re-enable screen updating
Application.ScreenUpdating = True
' Message box to cinform the update has completed
MsgBox "Complete"
    
    
' Else If there is a date but no queue selected then...
ElseIf Sheets("Report").Range("C6").Value = "" And Sheets("Report").Range("C5").Value <> "" Then
    ' Set FindDate as the desired date
    FindDate = Sheets("Report").Range("C5").Value
    ' If the selected date is on A sunday then...
    If Weekday(FindDate) = vbSunday Then
        ' Display error message
        MsgBox vbNewLine & "The selected date falls on a Sunday.  Please enter a valid date."
        ' Clear the date from C5
        Sheets("Report").Range("C5").ClearContents
        ' Select C5 ready for a new date to be entered
        Sheets("Report").Range("C5").Select
        ' Exit the sub
        Exit Sub
    End If
    ' Make sure the filter is not active on the Roster sheet
    Sheets("Roster").AutoFilterMode = False
    ' Activate the Roster sheet
    Sheets("Roster").Activate
    If Application.WorksheetFunction.CountIf(Sheets("Roster").Range(Cells(1, 6), Cells(1, LastCol)), FindDate) = 0 Then
        ' Display error message
        MsgBox vbNewLine & "The selected date does not exist in the Roster.  Please enter a valid date."
        ' Reactivate Report sheet
        Sheets("Report").Activate
        ' Clear the date from C5
        Sheets("Report").Range("C5").ClearContents
        ' Select C5 ready for a new date to be entered
        Sheets("Report").Range("C5").Select
        ' Exit the sub
        Exit Sub
    End If
    ' For each cell containing a date in the first row of the Roster sheet
    For Each Cell In Sheets("Roster").Range(Cells(1, 6), Cells(1, LastCol))
        ' If the cell value matches the desired date from the Report sheet then...
        If Cell.Value = FindDate Then
            ' Set Rng2 as the address of that cell
            Set Rng2 = Cell
            ' Exit the For loop early
            Exit For
        End If
    ' Check next cell
    Next
    ' Go back to the Report sheet
    Sheets("Report").Activate
    ' Update C7 with the value from row 313 of the corresponding coloumn on the Roster sheet
    Sheets("Report").Range("C7").Value = Sheets("Roster").Cells(Range("TotalAvailable").Row, Rng2.Column).Value
    ' Update C8 with the value from row 312 of the corresponding coloumn on the Roster sheet
    Sheets("Report").Range("C8").Value = Sheets("Roster").Cells(Range("TotalPercent").Row, Rng2.Column).Value
    
' Re-enable events
Application.EnableEvents = True
' Re-enable screen updating
Application.ScreenUpdating = True
' Message box to cinform the update has completed
MsgBox "Complete"
    
    
' Else if there is no date selected then...
ElseIf Range("C5").Value = "" Then
    ' Make sure the filter is not active on the Roster sheet
    Sheets("Roster").AutoFilterMode = False
    ' Clear the contents of C6:C8 of the Report sheet
    Sheets("Report").Range("C6:C8").ClearContents
    
' Re-enable events
Application.EnableEvents = True
' Re-enable screen updating
Application.ScreenUpdating = True
End If
End Sub

 

[everblazing]

I have to say I highly appreciate your assistance and time in this.
This was a very good learning and hope many people will also learn and benefit from your help.

 

[Fishboy]

I have to say I highly appreciate your assistance and time in this.
This was a very good learning and hope many people will also learn and benefit from your help.

Happy to help.