+ Reply to Thread
Results 1 to 10 of 10

complex formula for gathering Monthly data

  1. #1
    Registered User
    Join Date
    09-07-2012
    Location
    China
    MS-Off Ver
    Excel 2010
    Posts
    40

    complex formula for gathering Monthly data

    Please help me write this data into my book:

    I need to track the hours of people from different weeks(sheets) but also for the MONTH

    I need a formula to track the person and gather the info onto for them for the whole month.

    I have a formula for tracking the week (its under "actual hours" in the UTILITY sheet:
    Formula:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    But it only tracks one specific cell for each teacher.

    I need this new formula to track the DATES (column A) and SUM the "Facehours" (each teacher's 2nd column) for each teacher for the said month.

    *NOTE: the total for each day, on the schedule, is right under the day eg: row 54, 105, 156, 207, 258, 309 and 360

    This seems really hard for me! can anyone help?

    Here is an example book

    Attached Files
    Attached Files Attached Files

  2. #2
    Forum Contributor
    Join Date
    09-25-2004
    Posts
    269

    Re: complex formula for gathering Monthly data

    Can you shows us on a sample worksheet where & what results you are expecting?

  3. #3
    Registered User
    Join Date
    09-07-2012
    Location
    China
    MS-Off Ver
    Excel 2010
    Posts
    40

    Re: complex formula for gathering Monthly data

    Quote Originally Posted by vane0326 View Post
    Can you shows us on a sample worksheet where & what results you are expecting?
    In my attached woorkbook there is a overtime sheet. the sum of all of the face hours for the said month would be entered in there, accordingly.

  4. #4
    Forum Contributor
    Join Date
    09-25-2004
    Posts
    269

    Re: complex formula for gathering Monthly data

    Quote Originally Posted by Kram222 View Post
    In my attached woorkbook there is a overtime sheet. the sum of all of the face hours for the said month would be entered in there, accordingly.
    I only see 6 worksheets.

    1242
    1243
    1244
    1245
    Utility
    Monthly Hours

    There is no overtime worksheet.

  5. #5
    Registered User
    Join Date
    09-07-2012
    Location
    China
    MS-Off Ver
    Excel 2010
    Posts
    40

    Re: complex formula for gathering Monthly data

    Quote Originally Posted by vane0326 View Post
    I only see 6 worksheets.

    1242
    1243
    1244
    1245
    Utility
    Monthly Hours

    There is no overtime worksheet.
    sorry my meaning was the monthly hours sheet

  6. #6
    Forum Contributor
    Join Date
    09-25-2004
    Posts
    269

    Re: complex formula for gathering Monthly data

    Quote Originally Posted by Kram222 View Post
    sorry my meaning was the monthly hours sheet
    Thanks!

    In cell D3 should be 0.0 Right?
    In cell D4 should be 37.50 Right?

  7. #7
    Registered User
    Join Date
    09-07-2012
    Location
    China
    MS-Off Ver
    Excel 2010
    Posts
    40

    Re: complex formula for gathering Monthly data

    Quote Originally Posted by vane0326 View Post
    Thanks!

    In cell D3 should be 0.0 Right?
    In cell D4 should be 37.50 Right?
    No.
    D3 = 0
    D4 = 46.50

    Monthly hours sheet column D represents October
    so we only want the numbers added on all sheets that are within october.

    remember that undre each name, on the schedules, we want the numbers from the middle column

  8. #8
    Forum Contributor
    Join Date
    09-25-2004
    Posts
    269

    Re: complex formula for gathering Monthly data

    Quote Originally Posted by Kram222 View Post
    No.
    D3 = 0
    D4 = 46.50

    Monthly hours sheet column D represents October
    so we only want the numbers added on all sheets that are within october.

    remember that undre each name, on the schedules, we want the numbers from the middle column
    Let me see if I can come up with something.

  9. #9
    Registered User
    Join Date
    09-07-2012
    Location
    China
    MS-Off Ver
    Excel 2010
    Posts
    40

    Re: complex formula for gathering Monthly data

    Quote Originally Posted by vane0326 View Post
    Let me see if I can come up with something.
    Hi Vane0326,

    Did you figure anything out?

    anyone else wanna take crack at it?

  10. #10
    Forum Contributor
    Join Date
    09-25-2004
    Posts
    269

    Re: complex formula for gathering Monthly data

    Quote Originally Posted by Kram222 View Post
    Hi Vane0326,

    Did you figure anything out?

    anyone else wanna take crack at it?
    The way your data is layout I wasn't able to come up with a formula. BUT how about keeping "Face Hours" on different worksheets?

    Please look at the sample workbook below. I know it's not exactly what you want, but it's the only thing I could come up with.

    The formula is driven on the blue worksheet tabs.

    If you don't like this alternative method maybe one of the formula guru's on this forum can help you.
    Attached Files Attached Files
    Last edited by vane0326; 10-24-2012 at 11:20 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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