Efficient alternative to repeatedly entering data manually in Excel

[sanders456]

I have several payroll reports on different spreadsheets that I need to edit.

One of the columns has Employee ID Numbers, and another column has Unit Pay Rate. I need add the Unit Pay Rate Based on the number. The thing is, the employees and pay rates are mostly the same from pay period to pay period.



Is it possible to do this in a more efficient manner than manually adding them all?

[jeffreybrown]

So we can best help you, can you attach a sample worksheet with a before and after!

[ArviLaanemets]

In general, you need:
1. A table where employees are listed (e.g. tblEmployees: EmployeeID, Forename, LastName, [FullName], UnitID, ...);
2. A table for employees or units payrates history (e.g. tblUnitPayrates: UnitID, Payrate, ValidFrom;
3. A payroll table (e.g. tblPayrolls: PayDate, EmployeeID, [UnitID], Payrate, ...)

FullName in tblEmployees is calculated from ForeName and LastName.

In tblPayrolls, you enter pay date for every employee and select employee ID's (or employees full names). Unit ID's (when you want to have them - it will be easier to calculate other columns when having Unit ID available) and payrates depending on pay date are calculated.

When you need e.g. unit name in payroll table too, then you need a table e.g. tUnits: UnitID, UnitName.

Btw, instead of selecting EmployeeID, you can select FullName (and calculate EmployeeID and/or UnitID, when you need them).

When employees may move from one unit to another sometimes, all goes more complex - you need a table for employee movements, and the unit info must be calculated depending on paydate.