When will the Apply Names bug in Excel be fixed?

The Apply Names command loses parts of formulas and in certain cases Excel will crash.

This bug is in Excel 2010 and 2013, not in Excel 2007 or 2003.
To reproduce this, follow these steps. If you wish I can email you a workbook which shows the bug.
 
Enter 2 in B1 and 3 in B2
Enter =B1 in A4
Enter =B2 in A5

Give $B$1 the name "value_1"
Give $B$2 the name "value_2"

Enter the formulas below starting in B4, and then click Names > Apply Names and select both names.

=B2/B1
=$B$2/$B$1
=$B$2/B1
=B2/$B$1

In B4 The expected result is =value_2/value_1, what you get is just =value_2

Furthermore, if you UNcheck "Ignore relative/absolute" you get for

=B2/B1    unchanged, fine

=$B$2/$B$1    becomes =value_2/value_1, fine

=B2/$B$1     gives an error "Formula is too long. Formulas may not 
exceed 8192 characters"
(see also
http://www.excelforum.com/excel-general/738331-fomula-too-long-when-applying-names-to-ranges.html)

and
=$B$2/B1
gives "Microsoft Excel has stopped working"


It has previously been reported to Microsoft but no fix was announced:

Excel 2010 truncates formulas when applying names
http://answers.microsoft.com/en-us/office/forum/office_2010-excel/excel-2010-truncates-formulas-when-applying-names/418d6864-8fb1-4845-9686-eea6331e852f?msgId=9695513e-4615-498c-a5c7-31e1d0faa134
Excel 2010 truncates formulas when applying names


No fix has  been reported, and it's been known for two years now.
 
 

Question Info


Last updated February 5, 2019 Views 1,215 Applies to:
I agree with what you are saying. Why no fix I wonder?
http://people.stfx.ca/bliengme
A Guide to MS Excel 2013 for Scientists and Engineers

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

Thanks, Bernard. Have you reported it, as an MVP, you might have a reporting path?

I have posted it in the  Microsoft Partner Support Community as

http://partnersupport.microsoft.com/thread/458f71af-e3e4-4c5a-a07b-a6ab30294921

 

and got a response from Sean Li that it has been sent to the product team.

 

Also see

and

http://social.msdn.microsoft.com/Forums/en-US/exceldev/thread/e8cb0b2e-45b1-4651-9d35-6280f9f0d370/

 

I also contacted an MS employee I know but as it's not in their direct line I regret having to involve their time. There should be a bug report channel for Excel.

 

 

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

A bug report channel would be good but it would be full of things that are not bugs - there would be hundreds from folk who do not know about binary rounding errors for example.
http://people.stfx.ca/bliengme
A Guide to MS Excel 2013 for Scientists and Engineers

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

True.

And those who insert a row of data and are upset when the totals don't change.

 

I thought the Answers community would be an effective filter. I'm not clear on how one would escalate it, but it's quite normal to have first and second (and higher...) levels of support.

 

Thanks, Bernard,

 

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

Same bug in Excel 2016. Six years and no fix yet.

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

It appears to be fixed in XL 2016 build 16.0.6568.2025

best wishes

http://people.stfx.ca/bliengme
A Guide to MS Excel 2013 for Scientists and Engineers

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

Hmmm. My version is 16.0.6568.2036.

It's reproducible as shown above.

I have three versions of Excel installed: 2010, 2013, 2016.

Thanks for checking, Bernard,

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

Just an update.

Excel 2016 - 365 ProPlus, Version 16.0.6965.2117


I have MANY defined names, and applying all of them still glitches as originally described - but not predictably. So far only a small amount of cells (never know which ones) is incorrectly "reset".

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.