Apply Names in Excel is bugged?

Question

I see people had this problem back in 2005 and it's still not fixed.

In my sheet I have these named ranges:
A1 - "name1"
B1 - "name2"
C1 - "name3"

In some other 3 cells I have these formulas:

=A1
=A1+B1
=COUNT(A1:C1)

After running Apply Names... I expect to have this:

=name1
=name1+name2
=COUNT(name1:name3)

I never get that no matter what options I choose. Moreover, usually my formulas become completely ruined...

=name1
=name1
=name1

How could I get the result I need?
I am knowledgeable in VBA, so I tried to design a solution to this problem using VBA, but the code was not working either.

I'm using Excel 2013, 32-bit (MS Office Professional Plus). OS - Windows 7 Enterprise, 64-bit.

Answer 1

I can't replicate this (with my version of excel on Windows 7) -

If I define your names and then create formulas

Then apply names

I get the desired result -

How are you defining the names? I select the cell then click on its title to the left of the formula bar and type in the name.

---

With yours -

apply names -

As shown here

---

No change without "showing formulas" -

apply names

---

Okay, here we go. Office 2013, windows 7

apply names

Looks like a replicated failure.

---

Okay, let's try Excel 2016 on OSX Yosemite

Let's define our names and formulas -

Good, good, let's apply our names

What? Alert Formula is too long

Now it selected my count and.. what? Alert Microsoft Excel cannot find any references to replace

And it's a.. partial failure?

Okay then, let's do this manually -

Strange, it doesn't highlight the range, just the two cells?

But it works?

Just for comparison, a regular count highlights the range -

Answer 2

As was pointed out in code review, this will cause problems if, for instance, it's looking for "A1" and finds "A10" etc.

Okay, here's my attempt at a work-around. With this, your formulas must use absolute references all the time. It works on named ranges larger than 1 cell.

Please note that I'm searching usedrange - but you can narrow that down as you like by resetting srchRng.

Option Explicit
Sub FixNames()

Dim ClctNames As Variant
Set ClctNames = ActiveWorkbook.Names

Dim rngName As String
Dim rngNameLoc As String
Dim strFrmla As String

Dim c As Range
Dim n As Integer

'Define as needed
Dim srchRng As Range
Set srchRng = ActiveSheet.UsedRange

'For each name (n) in the collection
For n = 1 To ClctNames.Count

    'I'm storing the Named Range's name and address as strings to use below
    rngName = ClctNames(n).Name
    rngNameLoc = ClctNames(n).RefersToRange.Address

    '--Should I break this out into a function? If so, at what point?
    For Each c In srchRng
        'We only want to test cells with formulas
        If c.HasFormula = True Then
           'We have to check if the cell contains the current named range's address
           If InStr(1, c.Formula, rngNameLoc, vbTextCompare) <> 0 Then
              'Since these are perfect matches, no need to look for length or location, just replace
              strFrmla = Replace(c.Formula, rngNameLoc, rngName)
              c.Formula = strFrmla
           End If
        End If
    Next
Next

'No error handling should be needed

End Sub

You need to use absolute references because when I pull the named range's RefersToRange.Address it's returning a range object - not a range, so I'm setting it as a string. I guess you could write a function that removes the $ absolute references if you'd like.

_{that was a fun one}

Answer 3

I can't see it mentioned, but the exact formulas you give can be the issue.

They have no absolute referencing in them. So, if you create them while cell A2, say, is selected, and Apply Names, the functionality will work as well as your version of Excel allows. For me, version 2205, that is that it will offer to only apply name1 and name3, but not name2, and will successfully do so, even to the point of the second formula, A1+B1 becoming name1+B1. But it flat will not even offer to apply name2 under any circumstance I can find or create.

HOWEVER, if you leave that cell which was selected when you created the Named Ranges, that lack of absolute referencing means their Refers to formulas change to new addresses relative to the movement from that selected cell to the newly selected cell.

So name1's Refers to might change from =A1 to, say, =H3. And now the reference in the Named Range is NOT the same as the reference in the cell that has the formula =A1. So no change occurs.

Happens to me when I set it up with the relative referencing. As soon as I change it to absolute referencing, so name1 stays =A1 rather than changing, it applies that Named Range properly. As mentioned above, it goes into the second formula to apply it to the first reference, but not the second. And in the third, it nicely applies the two different names to the portions of the formula that match them.

So to get that much functionality, be sure you have absolute references in the Named Ranges: so =$A$1, not =A1.

However, I still could not get it to apply a Named Range to multiple cell addresses taken as a unit/single entity. So it was fine changing each part of $A$1:$C$1 to name1:name3, recognizing each cell in the single range address as matching a Named Range and converting each. But it would not recognize two referenced cells that were NOT part of a single reference entity as being a single thing replaceable by a Named Range that had both of them in it. That functionality seems to be absent.

As a side note, but an important one, I would mention that if you do not select (highlight) a range, but just have a single cell selected, Excel will to the Applying over the entire sheet. In the case where the references were not absolute, you may get extraordinarily unexpected results. In the example above, of a change in selected cell before Applying changing the Name's Refers to to =H3, any cell on the worksheet that has a reference to H3 will see it changed to the Named Range.

That may be a problem in and of itself. But worse, now that the change was made and that you did not see a change where you expected to see one, you may not use UNDO to undo anything that was done. You might go to the Named Range's definition and change it to A1 again, maybe with absolute referencing this time, maybe not, and then try again. And be happy when you see it work where desired. But the 14, say, formulas that used to look to cell H3 now will look to cell A1...

So select/highlight the cells you want the Applying to occur. That will keep stray application from happening.

Finally, for formula strings (or "pieces" if one prefers) that you realize will not adjust in this process, perform a Find and Replace on the places they may be to apply your nice new Named Ranges.

Answer 4

Say we start with:

and we already have assigned Names to A2 and B2. In the Formulas Tab, pull-down:

Define Name > Apply Names...

Make sure we hi-light both names and touch OK

and we get:

and so the Names get applied!