Forum Discussion

Steve Smith's avatar
Steve Smith
Copper Contributor
Aug 31, 2017

Named Range (Apply Names) formula being erased bug

There are several issues with named ranges - I'll start with the formula being erased problem.   PROBLEM #1 - FORMULA GETTING ERASED I've uploaded a sample file (testnamedrange3) to go with this e...
Formulas & Functions
NAMED RANGE
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Aug 31, 2017

Hi Steve,

 

Yes, #1 for pre-defined formulas exists for years, see for example https://answers.microsoft.com/en-us/office/forum/office_2013_release-excel/when-will-the-apply-names-bug-in-excel-be-fixed/0eb23d54-fbf0-4c64-8bad-cf52bc32547f?auth=1

 

Here https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/12836493-fix-the-apply-names-bug-that-corrupts-formulas-o it is defined as closed, but that's not correct.

 

Workaround if in Name Manager you change absolute references on relative one, i.e. change Input1 from Sheet1!$C$3 on Sheet1!C3.

 

Not sure about #2.

  • Steve Smith's avatar
    Steve Smith
    Copper Contributor
    Sep 01, 2017

    Sergei,

     

    Thanks for the response on number one - clearly a bug that has been around for a long time and not fixed.

     

    On #2 - it's really that 'Apply Names' doesn't work on any cell that has the full name (e.g. 'Sheet1'!C3 or 'Sheet1'!$C$3).  The apply names doesn't recognize it to change it to the name in any format (whether or not you change it in the name manager to eliminate the $).

     

    Thanks,

    Steve

Resources