How can I update (using VBA) a formula referencing a cell in column X on one spreadsheet when copying that row to another spreadsheet in the same workbook, but moving that particular cell to a different column, Y? Initially it was done automatically, but some undetermined action caused the link to break (I hope I got the terminology right)...

Thank you!