# Payroll,formulas and Macros?

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

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
Joe Bloggs
Tom Brown

hca Dept
Joe Bloggs
Tom Brown

and in the summary rows it has

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

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

G

#### group changes of spreadsheet

Hi Eduardo
thanks but it really does not tackle the issue i have