Forum Discussion
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 example - here's how to replicate it.
Create a simple spreadsheet. I have two input cells (Input1 and input 2). I have NOT named them yet.
Create other cells that refer to those cells via formula (e.g. look at cells c7-c9).
Now go give cells C3 and C4 names (like Input1 and Input2).
Now go on to the formulas tab and select 'Define Name' and then 'Apply Names'. I highlight Input1 and input2 and press 'OK'.
Rows 7 and 8 update correctly (the formulas now read "Input1 + Input2" and "Input1 * Input2"... It's row 9 that's the problem. Any additional calculations or formulas that were in the cell get erased. E.g. - originally the cell said c3*c4*100. After you apply names the c3*c4 exists -- but it has erased the *100 portion.
This only happens on the Windows version of Excel. It updates correctly on the Mac (I'm using the most recent versions of both - both 64-bit).
PROBLEM #2
If you have a cell that refers to another cell and includes the 'Sheet1' information in the formula (e.g. if you are referring to a prior sheet -- say something like "='Sheet1'!$C$3"). When you look at the named range you can see that the cell C3 (called Input1) actually has the full name exactly like the formula (e.g. Sheet1$c$3) -- but when you apply names it doesn't update this (whether it's on the same page as the named range cell or on a different page).
That's it for now - let me know if you have any additional questions on repeating these bugs.
2 Replies
- SergeiBaklanDiamond Contributor
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 SmithCopper Contributor
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