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

  • I would say that it's bad design to have Years/Months on separate sheets. An easier way would be to have a single sheet that stores data in a database format and another sheet that allows you to pick a month and year and the data is brought into that sheet. – user3357963 Feb 28 '13 at 22:52

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.

  • The second part of your answer is what I would do as well. – Kruug Feb 28 '13 at 18:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for?Browse other questions tagged or ask your own question.