Results 1 to 4 of 4
Thread: Timesheet Collation (2003)

20080526, 22:09 #1
 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.

20080527, 00:49 #2
 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 ctrlshiftenter):
=SUM(IF(($A$11:$A$1000="29556  Mgmt Project")*ISNUMBER(S11:T1000),S11:T1000))
D8array confim with ctrlshiftenter):
=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

20080527, 01:52 #3
 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

20080527, 02:42 #4
 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