Stop "Excel formula or sheet contains the name" dialog

When I want to copy a sheet (within the same file, so simply using left mouse button and Ctrl) in Excel 2007, I get this pop up dialog:

Yes / No dialog

where Author is a specific field somewhere in the sheet I'm editing.

The catch is that I cannot get rid of this dialog. If I press Yes, I get the same dialog with a different field. I have no idea how many fields there are in this sheet, but these dialogs just keep popping up...

If I press No, I get:

Name conflict

If I press Cancel (I don't even know what I'm changing, so I don't want to change it!), I get:

Name not valid

even though I pressed Cancel! (Same happens if I press OK without entering anything. If I enter something, I go back to the first dialog with the next field.)

Since I want to get out of this, I use Task Manager to quit Excel and then get:

Cannot quit Excel!

To prevent me banging my head on the table, can anyone tell me how I can change the Excel sheet so that these pop ups stop occurring?

Reinstate Monica - Goodbye SE

Posted 2012-01-12T14:55:33.230

Reputation: 1 237

If you ever come across countless dialogs that you just want to answer yes (or no) to, just hold Alt+Y or Alt+N or Alt+anotherletter, whichever the accelerator might be. You'll be done in seconds. – airstrike – 2016-09-01T18:40:53.837

What do you actually want to accomplish? Two copies of the same sheet in the same workbook? (to close excel, use task manager->end process for excel.exe) – soandos – 2012-01-12T15:06:23.767

Yes, two copies of the same sheet. Thanks for the Task Manager tip, that is exactly what I had to do. – Reinstate Monica - Goodbye SE – 2012-01-12T15:10:21.333

And so it's clear, you right clicked on the sheet tab, clicked move or copy, checked the create a copy box, picked your location, hit OK, and got these errors? – soandos – 2012-01-12T15:12:16.520

@soandos: actually, I just LMBed+Ctrl, I will update the question. – Reinstate Monica - Goodbye SE – 2012-01-12T15:14:15.803

Try it my way and see it if works. – soandos – 2012-01-12T15:16:01.837

@soandos: done it, same behaviour. – Reinstate Monica - Goodbye SE – 2012-01-12T15:17:52.740

2Are these things defined names on the workbook? If so, they will cause your error. – soandos – 2012-01-12T15:28:16.563

2The best way to handle the first message box is to click Yes. You will get that message for every similarly-defined & used name... Use the Name Manager to see how many named ranges you have. – Rachel Hettinger – 2012-01-12T17:31:09.373

@soandos: well, I guess they must be but it was not my spreadsheet so I don't know where they are. – Reinstate Monica - Goodbye SE – 2012-01-12T18:36:02.003

@RachelHettinger: thanks, how can I use the Name Manager? – Reinstate Monica - Goodbye SE – 2012-01-12T18:36:39.227

In the formula's tab, click name manager. – soandos – 2012-01-12T18:42:39.410

@soandos: thanks, that name manager is where I can delete all these references! If you make that into a good answer, I will accept it. – Reinstate Monica - Goodbye SE – 2012-01-13T10:01:24.683

Answers

Go to Formulas -> Name Manager, select the whole list in the pop up box and press Delete.

You may also need to close and re-open the file to get rid of these messages.

Reinstate Monica - Goodbye SE

Posted 2012-01-12T14:55:33.230

Reputation: 1 237

How do you get there since the menus are inaccessible ? – Overmind – 2018-01-05T06:08:49.507

@soandos - if you enter your own version of this answer, I will delete mine and accept yours. – Reinstate Monica - Goodbye SE – 2012-01-16T14:47:30.920

1With me it only worked when I close and reopen Excel after deleting the entries. – Gerhard Powell – 2014-04-11T19:28:28.060

I had the same problem, but Name Manager does not reveal all the names embedded in the sheet. Somehow I got a bunch of hidden names in the worksheet.

Here is how to unhide them

Sub ShowNames()  
  Dim nmName As Name  
  For Each nmName In ActiveWorkbook.Names   
    nmName.Visible = True  
  Next nmName  
End Sub

Now, you can see again all names in Name Manager and delete them.

Sandy Paternotte

Posted 2012-01-12T14:55:33.230

Reputation: 91

1I wish I could upvote this 20 times. – Ivan X – 2016-11-04T14:23:20.063

This error generally arises when there are some erroneous names in the worksheet. Press Ctrl+F3 (the Excel name manager box will show up). On the right hand side there will be a filter button - select "Names with error" and once all of them show up, delete the erroneous names.

Now try to copy the sheet.

Does it work?

Amruddin Haiderzada

Posted 2012-01-12T14:55:33.230

Reputation: 11