+ Reply to Thread
Results 1 to 15 of 15

Gathering data from separate sheets based on employee name & date

  1. 07-12-2014, 08:56 PM #1
    avidcat
    avidcat is offline
    Forum Contributor
    Join Date
    12-15-2005
    MS-Off Ver
    Office 2007
    Posts
    346

    Gathering data from separate sheets based on employee name & date

    I have a sample attached.

    Each sheet that is created will be labeled in the format mm-dd-yy.

    On the sheet labeled "Tracking" I want to gather the data from all other sheets created in the format above.

    Basically the sheets are used to track employee overtime. I want to gather all the data and see a overall daily overtime for each employee.

    The sample attached I manually plugged in the data for Employee #01 to show what I am looking to accomplish.

    I appreciate any help figuring out the formula's to do this.

    Thanks,
    Nick
    Attached Files Attached Files
    Last edited by avidcat; 07-13-2014 at 08:29 PM.
    Reply With QuoteRegister To Reply
  2. 07-13-2014, 01:02 AM #2
    Vikas_Gautam
    Vikas_Gautam is offline
    Forum Expert Vikas_Gautam's Avatar
    Join Date
    06-04-2013
    Location
    Ludhiana,Punjab, India
    MS-Off Ver
    Excel 2013
    Posts
    1,850

    Thumbs up Re: Gathering data from separate sheets based on employee name & date

    it was a bit tricky to make it automatic..
    but i managed to do it...
    check the attachment ....



    Say thanks, click *
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  3. 07-13-2014, 01:08 AM #3
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,929

    Re: Gathering data from separate sheets based on employee name & date

    Vikas, looking in your file, you ask for a private message if other columns are needed. Using private messages goes against what this forum is all about - we are all here to help and all here to learn, this can hardly happen if messages are being sent in private
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford
    Reply With QuoteRegister To Reply
  4. 07-13-2014, 01:19 AM #4
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,929

    Re: Gathering data from separate sheets based on employee name & date

    No need for complex array formulas here, a standard INDEX/MATCH will do what you need, just need to build in INDIRECT for the sheet names...
    B3=IFERROR(INDEX(INDIRECT("'"&TEXT(B$2,"mm-dd-yy")&"'!K4:K44"),MATCH(Tracking!$A3,INDIRECT("'"&TEXT(B$2,"mm-dd-yy")&"'!A4:A44"),0)),"")
    copied down and across
    Reply With QuoteRegister To Reply
  5. 07-13-2014, 07:46 PM #5
    avidcat
    avidcat is offline
    Forum Contributor
    Join Date
    12-15-2005
    MS-Off Ver
    Office 2007
    Posts
    346

    Re: Gathering data from separate sheets based on employee name & date

    Thanks for the replies. I misworded what I was looking to do. I wanted to gather a total amount of days each week that a employee went over 8 hours of overtime. Such as if they went over 8 hours 3 times that week the total for that week would be 3.

    Thanks for help on this.
    Nick
    Reply With QuoteRegister To Reply
  6. 07-13-2014, 08:27 PM #6
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,929

    Re: Gathering data from separate sheets based on employee name & date

    OK, try this instead...
    =IFERROR(COUNTIF(INDIRECT("'"&TEXT(B$2,"mm-dd-yy")&"'!C"&ROW()+1&":I"&ROW()+1),">8"),"")
    copied down and across
    Reply With QuoteRegister To Reply
  7. 07-13-2014, 08:28 PM #7
    avidcat
    avidcat is offline
    Forum Contributor
    Join Date
    12-15-2005
    MS-Off Ver
    Office 2007
    Posts
    346

    Re: Gathering data from separate sheets based on employee name & date

    That is perfect! Thank you.
    Reply With QuoteRegister To Reply
  8. 07-13-2014, 08:47 PM #8
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,929

    Re: Gathering data from separate sheets based on employee name & date

    Happy to help and thanks for the feedback
    Reply With QuoteRegister To Reply
  9. 07-13-2014, 09:30 PM #9
    avidcat
    avidcat is offline
    Forum Contributor
    Join Date
    12-15-2005
    MS-Off Ver
    Office 2007
    Posts
    346

    Re: Gathering data from separate sheets based on employee name & date

    Having a little trouble, is this gathering by name or by row? The sort order on sheets may put names in different orders.

    thanks,
    Nick
    Reply With QuoteRegister To Reply
  10. 07-14-2014, 12:22 AM #10
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,929

    Re: Gathering data from separate sheets based on employee name & date

    That was going by row, I figured your empl's would be in the same sequence.

    Use this instead, it will work even if the names are jumbled around...
    =IFERROR(SUMPRODUCT(--(INDIRECT("'"&TEXT(B$2,"mm-dd-yy")&"'!A4:A44")=Tracking!$A3)*(INDIRECT("'"&TEXT(B$2,"mm-dd-yy")&"'!C4:I44")>8)),"")

    I took it down from row 4 to row 44. Adjust this if needed
    Reply With QuoteRegister To Reply
  11. 07-14-2014, 01:04 AM #11
    avidcat
    avidcat is offline
    Forum Contributor
    Join Date
    12-15-2005
    MS-Off Ver
    Office 2007
    Posts
    346

    Re: Gathering data from separate sheets based on employee name & date

    Thanks that is working better, but for some reason if someone has all 8 hour days it still gives them a 1 for the week.
    Reply With QuoteRegister To Reply
  12. 07-14-2014, 01:13 AM #12
    avidcat
    avidcat is offline
    Forum Contributor
    Join Date
    12-15-2005
    MS-Off Ver
    Office 2007
    Posts
    346

    Re: Gathering data from separate sheets based on employee name & date

    Actually I am going to test, I have to adjust for the form I am using.
    Reply With QuoteRegister To Reply
  13. 07-14-2014, 01:41 AM #13
    avidcat
    avidcat is offline
    Forum Contributor
    Join Date
    12-15-2005
    MS-Off Ver
    Office 2007
    Posts
    346

    Re: Gathering data from separate sheets based on employee name & date

    Ok I got it to work, had to adjust my columns.

    One thing, it is counting text as 1. For example when a employee is on vacation we input PTO instead of hours in thier spot. I counts this and adds 1 for each occurance. Can it be adjusted to ignore text?

    thanks,
    Nick
    Reply With QuoteRegister To Reply
  14. 07-14-2014, 07:52 PM #14
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,929

    Re: Gathering data from separate sheets based on employee name & date

    Sorry for the delay

    Change to this...
    =IFERROR(SUMPRODUCT(--(INDIRECT("'"&TEXT(B$2,"mm-dd-yy")&"'!A4:A44")=Tracking!$A3)*(ISNUMBER(INDIRECT("'"&TEXT(B$2,"mm-dd-yy")&"'!$C$4:$I$44")))* (INDIRECT("'"&TEXT(B$2,"mm-dd-yy")&"'!C4:I44")>8)),"")
    Reply With QuoteRegister To Reply
  15. 07-14-2014, 08:02 PM #15
    avidcat
    avidcat is offline
    Forum Contributor
    Join Date
    12-15-2005
    MS-Off Ver
    Office 2007
    Posts
    346

    Re: Gathering data from separate sheets based on employee name & date

    Thank you, I really appreciate your help. It looks like that works perfect!
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Split excel data into multiple sheets based on Employee ID
    By Preeti1309 in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 04-21-2014, 02:33 AM
  2. [SOLVED] Returning the newest date entry for an employee on a separate sheet
    By avidcat in forum Excel General
    Replies: 4
    Last Post: 09-14-2012, 07:34 PM
  3. Gathering data based on Date & Time in cells.
    By avidcat in forum Excel General
    Replies: 7
    Last Post: 08-07-2011, 08:22 AM
  4. Extract data to separate sheets based on separate criteria
    By ryanw327 in forum Excel General
    Replies: 4
    Last Post: 11-22-2010, 12:57 PM
  5. Automate data gathering from separate worksheets
    By etpierce in forum Excel General
    Replies: 1
    Last Post: 07-14-2005, 05:45 PM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1