Forum Discussion

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...

TestNamedRanges3.xlsx9 KB

Formulas & Functions

NAMED RANGE

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

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