+ Reply to Thread
Results 1 to 2 of 2

building a reporting sheet which reports by date and shows values of another sheet

  1. #1
    Registered User
    Join Date
    09-29-2005
    Posts
    28

    building a reporting sheet which reports by date and shows values of another sheet

    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!

  2. #2
    Tom Ogilvy
    Guest

    Re: building a reporting sheet which reports by date and shows values of another sheet

    =SUM(('2005'!A2:A366=DATEVALUE("9/16/2005"))*('2005'!D2:F366="IN"))

    Entered with Ctrl+Shift+Enter rather than just enter (since this is an array
    formula) might be easier to manage. You could generalize it to

    look in another cell for the date and you can use indirect to make the sheet
    name variable.

    =SUM((Indirect("'" & B9 & "'!A2:A366")=B10)*(Indirect("'" & B9 &
    "'!D2:F366")="IN"))

    Where B9 would hold the year and B10 would hold the whole date stored as a
    date.

    Then you just have to update cells B9 and B10 with your code and don't have
    to update the formula.

    You can also use indirect with your existing formula if you want to
    calculate the row values and put them in cells.

    --
    Regards,
    Tom Ogilvy





    "alymcmorland" <[email protected]>
    wrote in message
    news:[email protected]...
    >
    > 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!
    >
    >
    > --
    > alymcmorland
    > ------------------------------------------------------------------------
    > alymcmorland's Profile:

    http://www.excelforum.com/member.php...o&userid=27652
    > View this thread: http://www.excelforum.com/showthread...hreadid=475005
    >




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1