Excel – How to get relative worksheet references when copying formulas in Excel

microsoft excel

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).

Best Answer

I would try to give a clearer answer, but I found the question to be a bit foggy.  I’m going to assume that each row on the JOHN sheet (i.e., each employee’s sheet) has a date on it somewhere; for example (assuming you use Row 1 for headings), Q2 might contain 1-Jan, Q32 might contain 31-Jan, Q33 might contain 1-Feb, etc.  So, try

=INDIRECT(TEXT(Q33,"mmm") & "!A$1:A$1")

The TEXT function extracts the month from the date, as a string.  & is string concatenation, so this builds the string "Feb!A$1:A$1".  And the INDIRECT function treats that string as a cell reference.

Just to be complete, you shouldn’t need to do 334 changes.  Just select Rows 33:60, do a Find and Replace, then select the rows for March, and so on.

Related Question