paul.hartland at fsmail.net
paul.hartland at fsmail.net
Fri Jun 11 02:47:22 CDT 2004
Jim, Thanks will give that a go a bit later. Paul Message date : Jun 11 2004, 12:55 AM >From : "Hale, Jim" To : "'Access Developers discussion and problem solving'" Copy to : Subject : RE: [AccessD] Help With Excel If Possible I have sent you an excel spreadsheet that does what you want using countif within excel. Basically you add a column to the first sheet to create a "key" like Hartland_Paul_ATT. On the second sheet you use the countif function to count the occurrences of the key. For the date range use an if statement on the key to determine if the date falls between the desired dates. If it doesn't set the "false" part of the if statement to an empty string "". Col A will only have keys that fall within the desired range and only these will be counted. HTH Jim Hale -----Original Message----- From: paul.hartland at fsmail.net [mailto:paul.hartland at fsmail.net] Sent: Thursday, June 10, 2004 6:47 AM To: accessd Subject: [AccessD] Help With Excel If Possible To all, I have a workbook that contains two spreadsheets, first spreadsheet is called Contract1 and has the following columns of information: Lastname, Firstname, DOW, JobNo, Code, Jobdate, Hours Hartland Paul Mon 12345 ATT 01/01/04 8 Hartland Paul Tue 12345 ATT 02/01/04 8 Hartland Paul Wed 12345 HOL 03/01/04 8 Hartland Paul Thu 12345 ATT 04/01/04 8 Hartland Paul Fri 12345 ATT 05/01/04 8 Hartland Paul Sat 12345 ATT 06/01/04 8 Hartland Paul Sun 12345 ATT 07/01/04 8 Williams Tom Mon 12345 HOL 01/01/04 8 Williams Tom Tue 12345 SICK 02/01/04 8 Williams Tom Wed 12345 ATT 03/01/04 8 Etc, etc DOW = Day Of Week (Mon, Tue etc), Code is either ATT, HOL or SICK. Everything on the first spreadsheet is ok. The second spreadsheet is called AdditionalData and has the following columns of information: Lastname, Firstname, Worked, Holiday, Sick Hartland Paul Williams Tom And that's about where I have got to, and this is where I need your help. I need to scan the Contract1 spreadsheet and where the Lastname and Firstname match from the AdditionalData spreadsheet and say date range 01/01/04-04/01/04 I need to populate the Worked, Holiday and Sick columns with the count of either ATT, HOL or SICK so that the result would be like this: Lastname, Firstname, Worked, Holiday, Sick Hartland Paul 3 1 0 Williams Tom 1 1 1 Thanks in advance to anyone that can help with this or point me in the right direction. Paul Hartland -- Whatever you Wanadoo: http://www.wanadoo.co.uk/time/ This email has been checked for most known viruses - find out more at: http://www.wanadoo.co.uk/help/id/7098.htm -- _______________________________________________ AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com -- _______________________________________________ AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com -- Whatever you Wanadoo: http://www.wanadoo.co.uk/time/ This email has been checked for most known viruses - find out more at: http://www.wanadoo.co.uk/help/id/7098.htm