I have a workbook containing two types of worksheets. One type is 12 worksheets, each referring to a month (JAN, FEB, MAR, etc.) Then I have another type of worksheets referring to employee's time cards (JOHN, MARY, PETER, etc.) containing each 12 months.
I input the start and end times for all employees for each day of the month in the MonthWorksheet (e.g. JAN), retrieve the relevant info for each employee in the employee's time card with VLOOKUP
and consequently calculate the time, overtime and salary.
All cells in the employee's cards referring to "JAN_worksheet" are well referenced and produce the expected result.
Now I would like to copy/paste the January format on the employee time card for February and so on.
How do I make the reference formula relative to, in my example, the FEB sheet and then the MAR sheet? When I copy, the formulas refer to the absolute worksheet JAN as in JAN!A$1:A$1
.
As the reference JAN!
is not relative, I have to manually amend all the formulas to make them refer to FEB (FEB!
) in the February time card. Quite tedious for all the months ahead, i.e. over 330 changes (365 days less January).