Hi, I have a quick assignment for somebody.
At the moment I have to manually update/remove names in who are on the payroll each month for new joiner, leavers and change of details. Would be good if could build a macro which updates the main spreadsheet from 2 different tabs, new joiners and leavers.
1)So firstly from the "New joiners" tab of [url removed, login to view] spreadsheet it would type over any vacancy row, matching reference number in column. If the reference number is another employee number then insert a new line under the old employee in the correct department (as id can be reported more than once). If there is no reference it means they are not replacing anyone and so a new line is inserted for them under the current department.
When a new line is inserted for new employee (either replacing old employee or brand new), the way to do it is copy and paste/insert the row above and put in their new id number, the formulas will update every other field automatically.
However there is one other thing it needs to do. Based on the new joiners start date and end date it needs to put 1's in column e-g. from the month of start date to the month end date (if no end date all year).
Before doing this paste the data from [url removed, login to view] into the New Joiners Tab and Employee Details Tab( this is what updates the formulas
2)The leavers tab of the [url removed, login to view] is slightly easier. Just look up employee number and and put zeroes the month after the month of leave date.
Also paste the correct columns in the leavers tab of Forecast Spreadsheet