Name manager won't let me rename a cell, but Find can't find it

[JenniferMurphy]

I put band width values in 10 cells and gave them names like BandWidthHighAM, BandWidthHighPM, BandWidthLowAM, etc. When it came time to use them in expressions, these long names became unwieldy, so I set about to shorten them. Several of the renamings went OK until I came to BandWidthOKPM. When I tried to rename it to BWOKPM, I got an error message saying,

Both the existing name,[sic] and the name you are attempting to use are currently referenced in cell formulas.

So I closed the Name Manager and did a search for "BMPMOK". It said it could not find any. I set it to search the entire workbook and to ignore case.

So how can a name be in use but not found by Find?

 

[braindiesel]

I have never seen that error and cannot imagine why you cannot do it.
Try
File - Options - Formulas
and set Workbook Calculation to Manual and see if that works.
You can chance the name of a range that has been used, same as you can type a name in a formula before you create the name... you just get a NAME error until the cell is named

 

[MARK858]

Have you tried tracing the dependents?

 

[JenniferMurphy]

I have never seen that error and cannot imagine why you cannot do it.
Try
File - Options - Formulas
and set Workbook Calculation to Manual and see if that works.

Nope, same result

You can chance the name of a range that has been used, same as you can type a name in a formula before you create the name... you just get a NAME error until the cell is named

Yes, but you cannot change a name to one that already exists. I just can't seem to find where that reference is.

 

[braindiesel]

Use your name manager. (Mid formulas ribbon)
Delete and create a new one or redifive the range there.

 

[JenniferMurphy]

Have you tried tracing the dependents?

How do I do that? I don't know which cell to trace?

I tried entering "=BWOKPM" so I could trace it, but that gets a #NAME error. So I have a name that gives me a #NAME error if I try to use it, but the Name Manager won't let me use. Is Excel just trying to drive me even more crazy?

 

[MARK858]

Click each of the cells with names, click the formula tab and click trace dependents.

 

[JenniferMurphy]

Use your name manager. (Mid formulas ribbon)
Delete and create a new one or redifive the range there.

Delete what? The name it says already exists is not listed in Name Manager and gets a #NAME error if I reference it.

 

[JenniferMurphy]

Click each of the cells with names, click the formula tab and click trace dependents.

This workbook has about 20 sheets, so there are hundreds, if not thousands, of cells with formulas. You want me to select each one individually and trace dependents?

I think I'll make a copy of the workbook and then delete sheets one at a time until the rename works.

 

[JenniferMurphy]

I made a copy of the workbook. When I opened it, I discovered that it has 76 additional sheets that are not in the original. They are Macro1 to Macro32 and Chart1 to Chart44.

The Macro sheets all seem to be empty. The Chart sheets all have a single blank chart.

What the #%$@& is going on?