Hello, I’ve encountered an odd error. I’m not sure if it’s human error, but here goes:
(attachment below, tldr: deleting sheet4 deletes all defined ranges, even ones never linked to it)
- The “results” sheet references tables using defined ranges. This workbook has existed for months, as have most of the ranges in the list.
- Today, I added some disposable sheets (sheet3, sheet4) to paste outside data. As it was over 2k new string values, I broke it up into chunks and used a couple of new defined ranges to help with transposing, sorting the data, deleting duplicates… I was doing all of this on disposable sheets so that I didn’t accidentally modify the existing data on the “tables” sheet.
- When it was all ready, I transposed the data from the new ranges into two of the original ones, “NamesTable1” and “NamesTable2”, and deleted the now-empty ranges.
- All looked good: Formulas were all still functional, and “sheet4” was now empty; so I deleted that sheet. On deleting the sheet, all defined ranges disappeared from the list, even those that did not receive new data (nor were at all referenced) during this process.
- Assuming I accidentally pasted some references, I rebuilt the two NamesTable ranges by pasting them to new columns as plain text, and then re-pasting those values back into the data ranges as plain text. This did not fix it, and I still couldn’t delete the empty sheet.
- Trying to narrow it down, I selected all cells in sheet4 and cleared all contents in case I somehow missed some content. No change. I deleted all columns to force a complete refresh of the sheet. No change.
- Deleting “sheet4” still deletes all defined ranges. I also tried moving “sheet4” to a blank workbook to see if it had somehow inherited all of the old ranges, but then neither file had defined ranges.
- Deleting “sheet3” has no effect, which is unsurprising since it was only used for text to columns shenanigans and never used any defined ranges.
- I did not look at any xml or user profile stuff, as I wouldn’t know where to begin.
I can rebuild the ranges I need, but I plan on repeating the good parts of this process in the future and I’d rather know if it’s a bug, or some error on my part.
Thank you for taking a look!
Names_errorcopy.xlsx (91.0 KB)