Find Defined Name(s) in Formula

[rjbinney]

I have a worksheet whose format and structure I like, but need to replace the underlying data.

I have defined name ranges.

When I looked in "Name Manager", I had some phantom ranges from when I replaced the data a few years ago. (e.g., I had "Sales", "Sales2021"). The "phantom" range (in this example, "Sales") points to a different workbook, and I can't imagine that I need it for this one. So I deleted it.

Now I want to re-name the active range in the current workbook. So I tried renaming "Sales2021" with just "Sales". I get a dialog: "Both the existing name, and name you are attempting to use are currently referenced in cell formulas."

Two questions:

1. I presumed that if I changed the Name, every formula that references that Name would update. Is that not true?
2. How do I find the cells that reference "Sales"? I tried a "Find" and it told me nothing did...

Thanks

(And a comment - the comma in the dialog box is unnecessary).

 

[Candyman8019]

To start, I would make a backup copy of your workbook.

Then, when you do a replace, you can specify that you want to look in formulas. For example:

 

[rjbinney]

Thanks. I was looking in formulas but omitted the exclamation point.

And boy howdy I only work in backups!

Thanks,