Named range in the Name Manager cannot be deleted

Question

The Problem

I have a bunch of named ranges in the name manager. Some of them referred to sheet, lets say, "list_data" named range referred to "data2!A1:A10" sheet. One time, I deleted the sheet "data2" where the "list_data" referred to. Then the named range become grey (specifically, the "referes to" tab), and it cannot be deleted.

Additional Info

The name itself can be edited. The sheet and workbook is unprotected.

P.S. I'm not sure why I can't upload the screenshot, it always failed

Answer 1

When you delete a sheet that is referred to by a named range you simply get a #REF! error in the Name Manager. Your screenshot reveals no such #REF! errors. Note that the name that is selected in your screenshot "cat_comp1" refers to a structured table. These can't be deleted via the Name Manager. Hence, the 'delete' button is greyed out.

What happens when you select the name "comps". The one with the icon with the two blue squares in it? That's a named range and the 'delete' button should become active, just as it will be active for any other named range (same icon type), even when they contain #REF! errors.

Answer 2

I can't replicate the situation that you describe where a sheet with tables is deleted (or hidden) but that the table name(s) from that sheet remain(s) in the Name Manager. when I delete a sheet with tables the table names disappear from the Name Manager.

Answer 3

Finally, I can upload the pic here.

Here's the pic of above's problem

Answer 4

Hi Nursahid A!

I am Shakiru, an independent advisor and a user like you, and I am glad to be helping you out today.

When you delete a sheet that is referenced by a named range in Excel, the named range may become "broken" or "invalid," resulting in the inability to delete it using the usual methods.

To resolve this issue, you can try the following steps:

* Open the Name Manager: Go to the Formulas tab in the Excel ribbon and click on "Name Manager" in the Defined Names group. Alternatively, you can press the shortcut key combination Ctrl+F3 to open the Name Manager.

* Locate the broken named range: In the Name Manager dialog box, scroll through the list of named ranges and find the one that is marked as "invalid" or has a grayed-out "refers to" tab. This is the named range that is referencing the deleted sheet.

* Edit the named range: Select the invalid named range and click on the "Edit" button. The Edit Name dialog box will appear.

* Update the reference: In the Edit Name dialog box, update the reference to a valid range or remove it entirely. You can either change the reference to a range in an existing sheet or delete the reference if it is no longer needed. Click "OK" to save the changes.

* Delete the named range: After editing the named range, go back to the Name Manager dialog box and select the now-edited named range. Click on the "Delete" button to remove it

Kindly let me know, if you require additional assistance, I will be glad to help further.

Best Regards, Shakiru