Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    May 2008
    Location
    Maryland, USA
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Timesheet Collation (2003)

    I am trying to design a form where users can copy their timesheets onto an Excel worksheet with a limited amount of clicks and data entry. The attached worksheet has two tables (13:19) and (22:28) of timesheet data as an example. This is the format that the timesheet would be pasted into Excel directly from the html timesheet. I inserted the calculation and format to S20 and T29.

    I would like D110 update with the newest timesheet data as new timesheet tables are added.

    Thanks
    Huck

    I would like to click a button (the blue object) to insert new rows.
    Attached Files Attached Files

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Timesheet Collation (2003)

    If a new sheet is added, where is d1:d3 supposed to get the data from? Is it always this particular set or will that data change?

    In D9 you seem to be deducting the weekends from the upper section an extra time since in D3 they are already deducted, Is this how it is supposed to be done or is D9 supposed to be a count of the number of workdays in all the datasets?

    The datasets you list have no numbers (they are text that looks like a number) in them (except the total column). Is this goiing to always be true?

    For the collective numbers (D79) I can get them in your example with the formulas (change the 1000 to how large you think the range may be):

    D7 (array confim with ctrl-shift-enter):
    =SUM(IF(($A$11:$A$1000="29556 - Mgmt Project")*ISNUMBER(S11:T1000),S11:T1000))

    D8array confim with ctrl-shift-enter):
    =SUM(IF(($A$11:$A$1000="1000 - PERSONAL")*ISNUMBER(S11:T1000),S11:T1000))

    In D9:
    =8*(COUNTIF(D11:T1000,"Mon")+COUNTIF(D11:T1000,"Tu e")+COUNTIF(D11:T11000,"Wed")+COUNTIF(D11:T1000,"T hu")+COUNTIF(D11:T1000,"Fri"))
    (In D9 I get 176 not 144 since the 4 weekends in the first section are 32 days extra you deduct)

    If D14 are meant to reflect the first dataset it does not need to be changed. If it must reflect all but the last one, then it gets more complicated. If that is the case it probably could be done with 2 sets of dynamic range names: the complete set and all but the last one. I would key the length as the location of the last TOTAL in column B and the 2nd to the last and define the names with OFFSET.

    But before I create them, I need some of the questions answered as to the logic...

    Steve

    Steve

  3. #3
    3 Star Lounger
    Join Date
    Jul 2007
    Location
    United States
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Timesheet Collation (2003)

    When new data is added, D13 should reflect the new data.

    The weekends should be deducted. Monday through Friday workdays are the denominator minus Personal days and Holidays.

    It is t.rue that the pasted data sets have no numbers. That is, they are pasted as text. I was thinking about running a formatting macros on the range of the data, but haven't gotten their yet.

    For D7, I am going to have to create a reference table on another sheet that a listing of project numbers. "29556 - Mgmt Project" is only one project that is charged to, in reality, there will be a list of approximately 20 some projects. I was thinking of a VLOOKUP within the formula to accommodate that.

    I'll try to integrate the formulas you have suggested and get back to you.

    Thanks
    Huck

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Timesheet Collation (2003)

    If D13 should reflect the new data, why does the example use the older data and not the newer data?

    If you format the cells to be numbers the formulas will not work correctly. They depend on the total being in either S or T and only one have numbers in it. If the text numbers have the characters removed (to convert from numbers to text) I would sum all D: T and divide the total by 2 (since all the numbers are duplicated in the total).

    I don't understand what you want done with the lookup...

    Perhaps you could provide a different example with a couple additional datasets and how the lookup will work (as well as the values instead of text if that is what you want the formulas to work with)

    Steve

Posting Permissions

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