Hello, I want to reference data from every other cell from one workbook into another.

For example, This is the forumla that I have in cell A1:
='S:\Payroll\2011\[Crew Time by Day of Week 1.15.11.xls]NE CORP'!$A$42

In Cell B1, I want the formula to say:
='S:\Payroll\2011\[Crew Time by Day of Week 1.15.11.xls]NE CORP'!$C$42

However when i drag the fomula across row 1, it autofills the exact same formula that is in A1 without changing the referenced cell at all.

How can I do this? I am in somewhat of a time crunch since this is a report I am doing for my boss. I appreciate any tips. Thank you.