| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Payroll,formulas and Macros?
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Payroll,formulas and Macros?
Hi
Look CPearson web, he has excellent examples related with payroll http://www.cpearson.com/excel/overtime.htm http://www.cpearson.com/excel/topic.aspx "group changes of spreadsheet" wrote: 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Payroll,formulas and Macros?
Hi Eduardo
thanks but it really does not tackle the issue i have "Eduardo" wrote: Hi Look CPearson web, he has excellent examples related with payroll http://www.cpearson.com/excel/overtime.htm http://www.cpearson.com/excel/topic.aspx "group changes of spreadsheet" wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Adding payroll stubs payroll calculator | New Users to Excel | |||
| formulas vs macros | Excel Discussion (Misc queries) | |||
| Macros within formulas | Excel Programming | |||
| links for formulas between two worksheets, payroll carryover acrossmonths | Excel Worksheet Functions | |||
| Macros/Formulas | Excel Worksheet Functions | |||
![group changes of spreadsheet[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
