J
Joel
Hi Everyone,
Can anyone help me with this query in Excel 2003?
Background
I have a spreadsheet that keeps track of employee holidays, for example in
column “A†I have list of Employee names in column “D†I have the number of
holidays that that person is entitled to, Column “E†the number of Holidays
taken which is subtracted from “D†and then a few other bits and pieces .
Column “N†I have the a date Sun 01/04/2006 that then follows on for 6
months to Sat 30/09/2006
And the underneath that column I have the exact setup with the send half of
then year continue from the upper row.
I Have a couple of employees in already an to account for holidays an sick I
use countif statements each employee as there own row with in both parts of
the year one on top and on below for the second half of the year.
Example countif below For employee 1
=COUNTIF(N10:GM10,"s/2")/2+(COUNTIF(N31:GM31,"s/2")/2)+COUNTIF(N10:GM10,"s")+(COUNTIF(N31:GM31,"s"))
Example for Employee 2
=COUNTIF(N11:GM11,"s/2")/2+(COUNTIF(N32:GM32,"s/2")/2)+COUNTIF(N11:GM11,"s")+(COUNTIF(N32:GM32,"s"))
Question
Based on the above information
If I had ten Employees how would I write the VBA to insert a new row at
underneath the last one with “Name†as there name and follow the same countif
statement above but for that person
So for example it would be
=COUNTIF(N12:GM12,"s/2")/2+(COUNTIF(N33:GM33,"s/2")/2)+COUNTIF(N12:GM12,"s")+(COUNTIF(N33:GM33,"s"))
And this would continue ever time a pressed “New Person it would insert a
line for them with there correct countif statements
Can it be done with Code on a command button if so can you please help
Can anyone help me with this query in Excel 2003?
Background
I have a spreadsheet that keeps track of employee holidays, for example in
column “A†I have list of Employee names in column “D†I have the number of
holidays that that person is entitled to, Column “E†the number of Holidays
taken which is subtracted from “D†and then a few other bits and pieces .
Column “N†I have the a date Sun 01/04/2006 that then follows on for 6
months to Sat 30/09/2006
And the underneath that column I have the exact setup with the send half of
then year continue from the upper row.
I Have a couple of employees in already an to account for holidays an sick I
use countif statements each employee as there own row with in both parts of
the year one on top and on below for the second half of the year.
Example countif below For employee 1
=COUNTIF(N10:GM10,"s/2")/2+(COUNTIF(N31:GM31,"s/2")/2)+COUNTIF(N10:GM10,"s")+(COUNTIF(N31:GM31,"s"))
Example for Employee 2
=COUNTIF(N11:GM11,"s/2")/2+(COUNTIF(N32:GM32,"s/2")/2)+COUNTIF(N11:GM11,"s")+(COUNTIF(N32:GM32,"s"))
Question
Based on the above information
If I had ten Employees how would I write the VBA to insert a new row at
underneath the last one with “Name†as there name and follow the same countif
statement above but for that person
So for example it would be
=COUNTIF(N12:GM12,"s/2")/2+(COUNTIF(N33:GM33,"s/2")/2)+COUNTIF(N12:GM12,"s")+(COUNTIF(N33:GM33,"s"))
And this would continue ever time a pressed “New Person it would insert a
line for them with there correct countif statements
Can it be done with Code on a command button if so can you please help