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?

 

[MARK858]

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?

Information than you hadn't given and answers proposed are based on the information given.

that it has 76 additional sheets that are not in the original. They are Macro1 to Macro32 and Chart1 to Chart44.
What the #%$@& is going on?

I am afraid I haven't come across behaviour like that without macro's being involved.

On the original workbook with a blank sheet open and active try running the code below by NateO. Does it throw up anything strange (please note we might have to play with the ranges as it is old code)?

Sub Rprt()
Dim nm As Name, n As Long, y As Range, z As Worksheet
Application.ScreenUpdating = False
Set z = ActiveSheet
n = 2
With z
    .[a1:g65536].ClearContents
    .[a1:D1] = [{"Name","Sheet Name","Starting Range","Ending Range"}]
    For Each nm In ActiveWorkbook.Names
         On error resume next
        .Cells(n, 1) = nm.Name
        .Cells(n, 2) = Range(nm).Parent.Name
        .Cells(n, 3) = nm.RefersToRange.Address(False, False)
        n = n + 1
        On error Goto 0
    Next nm
End With

Set y = z.Range("c2:c" & z.[c65536].End(xlUp).Row)
y.TextToColumns Destination:=z.[C2], DataType:=xlDelimited, _
    OtherChar:=":", FieldInfo:=Array(Array(1, 1), Array(2, 1))
[a:d].EntireColumn.AutoFit

Application.ScreenUpdating = True
End Sub

 

[JenniferMurphy]

Information than you hadn't given and answers proposed are based on the information given.

I try to provide all of the information that I think might be relevant.

I am afraid I haven't come across behaviour like that without macro's being involved.

I haven't written any macros for this workbook, but there are several UDFs in a code module. It's an xlsm workbook.

On the original workbook with a blank sheet open and active try running the code below by NateO. Does it throw up anything strange (please note we might have to play with the ranges as it is old code)?

I ran the macro. It ran for several minutes. I copied the output to the workbook in this DropBox folder:

Dropbox

www.dropbox.com

 

[MARK858]

Sorry Jennifer but can you try running the code below and then run the previous macro again as I can't see the issue in the previous run.
Make sure that no other workbook is open please when you run the codes.

Sub unhideAllNames()
For Each tempName In ActiveWorkbook.Names
        tempName.Visible = True
    Next
End Sub

 

[JenniferMurphy]

I found the sheet that appears to be causing the problem, but it doesn't answer any questions for me.

After I deleted the sheet named "Chart 50", I was able to rename the cell from "BandWidthOKPM" to "BWOKPM. That sheet contains a table and a chart generated from the data in that table. Here are the first few rows of that table:

Column C contains dates. That's the X axis. Columns D & E contain blood glucose readings. They are integers between about 100 and 350. They generate two Line charts. These 3 columns contain ;iteral data.

Columns F-O contain band widths. They generate horizontal bands on the chart using the stacked area chart type. Each cell in these columns contains an expression referencing a global bandwidth name. For example, the cells in Column K all contain the expression "=BWOKAM". That used to be a longer name, but I was able to rename it, as well as all of the others but one. All of the cells in Column L contain the expression, "=BandWidthOKPM". That's the one I could not rename.

After deleting this sheet, I was able to rename that one, too. It is not referenced anywhere on this sheet as far as I can tell.

 

[braindiesel]

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.

I believe the suggestuon was to use one to try and find the source of your problem... not individual formulas to correct

 

[JenniferMurphy]

Sorry Jennifer but can you try running the code below and then run the previous macro again as I can't see the issue in the previous run.
Make sure that no other workbook is open please when you run the codes.

OK. I closed all workbooks, opened the one in question, added a new blank sheet, ran the unhideAllNames macro, ran the Rprt macro, and moved the result to a new sheet in the Dropbox workbook. The output looked the same as before, so I created a 3rd sheet comparing the rows. They are indeed all equal.

While I was fiddling with that, my fat fingers hit some key combination that caused another Chart1 sheet to appear. I moved that to the Dropbox workbook.

I think the key combination I hit was something like Ctrl+F9 or Ctrl+F10, but I can't reproduce it. I have a Surface Book. The geniuses at M$FT designed it without a function key row. The function keys are jammed onto the top row and I have to activate the Function key to use them. Grrrrrr. F9 shares a key with End. I am constantly forgetting that the Function key is active and pressing what I think is End, but getting F9 instead -- or the other way around. Double Grrrrrrrrr.

Here's that link again:

Dropbox

www.dropbox.com

Thanks

 

[MARK858]

I think the key combination I hit was something like Ctrl+F9 or Ctrl+F1

On a PC selecting data and pressing F11 creates a chart sheet so I would think it is similar on a Surface Pro. I will have a look at the file when I knock off work tonight.

 

[JenniferMurphy]

On a PC selecting data and pressing F11 creates a chart sheet so I would think it is similar on a Surface Pro. I will have a look at the file when I knock off work tonight.

OK, thanks. I was probably trying to hit F11 (I meant to say F9 or F10). And that probably has nothing to do with the naming problem.

I guess I must have done something similar to create the 44 Chart-n sheets when I made a copy of the workbook, but I sure don't remember pressing F11 44 times, Maybe I pressed and held and the typematic (Is that the right name) repeated it 44 times. But that doesn't explain the 32 Macro-n sheets.

I just tried copying the original workbook again. None of those extra sheets were created. Maybe I'm hallucinating?????