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

Solution 1:

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.