I am trying to copy to sheets that are referenced to each other. They are referenced using local level named ranges. An example of one formula in the sheet is...

=IF(B19="","",VLOOKUP(B19,TempDB!data1,3,FALSE))

The name of the active sheet would be "Temp".

In Excel 2003 I could select the two sheets and choose to copy them and it would automatically update the reference to the new sheet names. For example if the new sheets were named "Full" and "FullDB" then my formula would automatically be updated to look like

=IF(B19="","",VLOOKUP(B19,FullDB!data1,3,FALSE))

However in Excel 2007 it does not update the references in this manner and leaves my formulas with the same references that they had in the original sheet. Why does it do this and is there a way that I can get it to update them automatically in 2007.

thanks