Hi,

ok i have an attendance database, one page has dates in the first column in an ascending order and the to row has names and then that goes from left to right, organized by teams. Filling in individual values per day under each individual name who is ill in and on holiday and so on.

I have built a reporting page to report on per team who is in ill and on holiday but by day, so the user will put in the date and then the reporting values will change to that day, the only thing is, is i don't know how to do that so thats why i need someones expertise!

i have code for my main page where the user enters the date they wish to go to and then the program takes them straight to that date:

Sub Macro_Main()
'
' Macro_Main Macro
' Macro recorded 13/09/2005 by Aly McMorland
'

Dim d As Date, yr As String, i As Integer
d = InputBox("Please enter a date e.g. 16/09/2005, the format of the date must be accurate")
yr = Year(d)
i = d - DateValue("12/31/" & yr - 1)
If yr = 2005 Then
Sheets(yr).Activate
Range("A" & 5 + i - 212).Select
Else: Sheets(yr).Activate
Range("A" & 5 + i).Select
End If

End Sub

The reason why it has a If yr = 2005 then is because in the 2005 tab sheet it is only from August to the end of the year, where as the other years have full sheets from january to december.

So what i need is a modification of the above code so that it will change the values in my reporting table, basicaly COUNTIF statements but to change those to the day that the user will specify, so there could be = COUNTIF ('2005'!D6:F6,"IN") but the user changes the day so the countif statement would change to = COUNTIF ('2005'!D10:F10,"IN")

or

if that is too hard as i think it might, i could create a table with all the values in that it could possibly be, the statement would now refference that new table and depending on what the user puts in depends on the values shown from this table.

But if anyone could help that would be great!


thanks!