Here's the situation

I have a number of sheets that record a persons work times - person 1, person 2, person 3

The same range of cells are extracted from multiple sheets and form an array for a lookup function by date worked

Date worked 20th June 2014
Person 1 2 hours
Person 2 3 hours
Person 3 4 hours

When a person leaves, I want to archive their worksheet by renaming it to their real name - keeping the linked cells for that date in the new name,
then making a copy of it, erasing the time worked data in it, and renaming the copy back to the generic Person 1 - and maintain the linked cells of the renamed back to the original named sheet in the lookup array.

Date worked 20th June 2014
John Smith 2 hours
Person 2 3 hours
Person 3 4 hours

Date worked 21th June 2014
Person 1 0 hours
Person 2 3 hours
Person 3 4 hours




Over time I want the array not to expand in size - just link to renamed sheets for dates past, and to the generic sheet for present time

My problem is that when I do this manually, the linked cell references after renaming get the #REF error in the array



Link to DOWNLOAD THE SPREADSHEET

https://drive.google.com/file/d/0BxP...it?usp=sharing