G
group changes of spreadsheet
I have a problem with macros to help a payroll.Its a three stage process so
that information can be imported into sage payroll.
I have staff who work different shifts week day,night,weekends and public
holiday and for different departments.
So for example
Admin dept
Joe Bloggs
Tom Brown
work monday tuesday,wedensday eveing and saturday.
HCA Department
Joe Bloggs
Tom Brown
do hours in this department in the week as well
So the Column A Has Dept Name followed by Staff
Row are then each day of the month and split into days,nights and weekend
and public holiday and sub
Someone fill in the rota the hours worked by each staff ,in each department
against each day.The final rows calculates totals worked by each staff in
each department which is sub divided by another subdept).
this is calculated manually by just having formula sum(cell1,cell5,cell 6)
etc.
1/ I want to change this so that formula is more fool proof, ie does not
rely on inputting the correct cell in the sum -perhaps an offset?
Once this is done , we have a summary in the sheet which shows in the first
column
Admin dept
Joe Bloggs
Tom Brown
hca Dept
Joe Bloggs
Tom Brown
and in the summary rows it has
Admin
HCA
Admin Std Days Nights W/e PH Std Days Nights
W/e PH
Joe Bloggs 6 7 8 10
Tom Brown 12 12 12 13
hca dept
Joe Bloggs 7
10 13 15
Tom Brown 20
15 16 17
Step 2 .Using sumif formula in a new sheet the above information is shown as
before in th ecolum but in row by sub department, so for example th eformula
would be
sumif(sheet 1 a:a,sheet 2 a1,sheet 1 d:d)+ sumif(sheet 1 a:a ,sheet2 a1,
sheet 1g:g) so left with
sUB dEPT 1 sUB dEPT 2
Admin DAY NIGHT W/E PH DAY NIGHT W/E PH
Joe Bloggs 5 6 7 10 4 10 6 3
Tom Brown 5 7 10 12 ETC ETC
sub dept 1 sub dept 2
day night w/e day night w/e ph
hca
Joe Bloggs
tOM bROWN
Again i want to be able to perhaps not use sumif but instead have a formual
that uses the sub dept and the type of shift eg day,night,w/e?
Final Process is to get this information in a format suitable to import into
Sage Payroll
So i have a spreadsheet with names in column A that are repated if they have
worked for different sub departments and th ehours they have done for each
sub department is broken down in rows.The format i am looking for is a
spreadsheet which has in
column 1, the name found from row a
column 2 ,the department which are in column A, above the staff in the
department, so it looks like
Admin dept
Joe Bloggs
Tom Brown
hca Dept
Joe Bloggs
Tom Brown
Column 3 Sub Department which is say in row 3
Column 4 IS TYPE EG DAY, NIGHT,W/E PH which is shown in row 4 but is
repeated under sub depts
Column 5 the hours worked by Staff member in the the department and sub
department fo rthe type eg day work
All very complicated ,much easier to show by spreadsheets but cannot link to
this
that information can be imported into sage payroll.
I have staff who work different shifts week day,night,weekends and public
holiday and for different departments.
So for example
Admin dept
Joe Bloggs
Tom Brown
work monday tuesday,wedensday eveing and saturday.
HCA Department
Joe Bloggs
Tom Brown
do hours in this department in the week as well
So the Column A Has Dept Name followed by Staff
Row are then each day of the month and split into days,nights and weekend
and public holiday and sub
Someone fill in the rota the hours worked by each staff ,in each department
against each day.The final rows calculates totals worked by each staff in
each department which is sub divided by another subdept).
this is calculated manually by just having formula sum(cell1,cell5,cell 6)
etc.
1/ I want to change this so that formula is more fool proof, ie does not
rely on inputting the correct cell in the sum -perhaps an offset?
Once this is done , we have a summary in the sheet which shows in the first
column
Admin dept
Joe Bloggs
Tom Brown
hca Dept
Joe Bloggs
Tom Brown
and in the summary rows it has
Admin
HCA
Admin Std Days Nights W/e PH Std Days Nights
W/e PH
Joe Bloggs 6 7 8 10
Tom Brown 12 12 12 13
hca dept
Joe Bloggs 7
10 13 15
Tom Brown 20
15 16 17
Step 2 .Using sumif formula in a new sheet the above information is shown as
before in th ecolum but in row by sub department, so for example th eformula
would be
sumif(sheet 1 a:a,sheet 2 a1,sheet 1 d:d)+ sumif(sheet 1 a:a ,sheet2 a1,
sheet 1g:g) so left with
sUB dEPT 1 sUB dEPT 2
Admin DAY NIGHT W/E PH DAY NIGHT W/E PH
Joe Bloggs 5 6 7 10 4 10 6 3
Tom Brown 5 7 10 12 ETC ETC
sub dept 1 sub dept 2
day night w/e day night w/e ph
hca
Joe Bloggs
tOM bROWN
Again i want to be able to perhaps not use sumif but instead have a formual
that uses the sub dept and the type of shift eg day,night,w/e?
Final Process is to get this information in a format suitable to import into
Sage Payroll
So i have a spreadsheet with names in column A that are repated if they have
worked for different sub departments and th ehours they have done for each
sub department is broken down in rows.The format i am looking for is a
spreadsheet which has in
column 1, the name found from row a
column 2 ,the department which are in column A, above the staff in the
department, so it looks like
Admin dept
Joe Bloggs
Tom Brown
hca Dept
Joe Bloggs
Tom Brown
Column 3 Sub Department which is say in row 3
Column 4 IS TYPE EG DAY, NIGHT,W/E PH which is shown in row 4 but is
repeated under sub depts
Column 5 the hours worked by Staff member in the the department and sub
department fo rthe type eg day work
All very complicated ,much easier to show by spreadsheets but cannot link to
this