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

Resources