Replace sheet in workbook with new sheet without #REF formula errors

[ExcelNewbie2020]

Hello everyone,

whenever I have a workbook that pulls info from multiple worksheets, if I have to replace one of the worksheets with an updated version and name the sheet with the same name as the older sheet, and then delete or change the name of the older sheet, all the formulas that were referencing the older sheet instead reference #REF.

Is there anyway to keep all the formula references the same, and simply swap out the sheets and delete the old sheet without this error?

Thanks in advance.

[teylyn]

No, you can't swap out sheets like that. Consider keeping all sheets and just pasting new data into existing sheets.

Or, keep the sheets with the data in a different workbook than the sheet with the formulas. Close the workbook with the formulas, then swap out and rename the sheets, and re-open the workbook with the formulas. As long as the formula workbook is closed, it will not detect that you deleted and renamed any sheets and the formulas will find the sheets.

[hrlngrv]

. . . Is there anyway to keep all the formula references the same, and simply swap out the sheets and delete the old sheet without this error? . . .

Not directly. However, if the replacement worksheet would have exactly the same layout (cell formatting, row heights, column widths, data locations by cell) as the original worksheet, you should be able to copy all cells in the replacement worksheet and paste into the original worksheet. Or clear contents of all cells in the original first, then paste in everything from the replacement worksheet.

Alternatively, if the original worksheet to be replaced were named MyWorksheet, you could add the new worksheet either immediately before or immediately after it, rename MyWorksheet as Replace_Me, and rename the new worksheet as MyWorksheet. At this point formulas in other worksheets would refer to Replace_Me instead of MyWorksheet.

Press [Ctrl]+H to display the Find and Replace dialog, replace all instances of Replace_Me! with MyWorksheet! in all cell formulas. Note: if your actual worksheet names require single quote delimiters, include them in this operation. This should change all formulas in other worksheets to refer to the replacement worksheet, and no formulas should refer to Replace_Me. Now delete the Replace_Me worksheet.