Formulas with external cell references not working`

%3CLINGO-SUB%20id%3D%22lingo-sub-61262%22%20slang%3D%22en-US%22%3EFormulas%20with%20external%20cell%20references%20not%20working%60%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-61262%22%20slang%3D%22en-US%22%3E%3CP%3ESomething%20has%20recently%20happened%20where%20I%20am%20unable%20to%20create%20formulas%20in%20one%20worksheet%20that%20reference%20cells%20in%20another%20worksheet%20unless%20I%20manually%20type%20the%20cell%20reference.%20%26nbsp%3BIf%20I%20try%20to%20create%20those%20formulas%20with%20external%20cell%20references%20by%20clicking%20in%20the%20reference%20cell%20in%20the%20external%20worksheet%2C%20some%20other%20random%20cell%20(or%20set%20of%20cells)%20is%20highlighted%20in%20the%20external%20worksheet%20and%20when%20I%20press%20Enter%20to%20complete%20the%20formula%2C%20it%20just%20returns%20a%20blank%20cell%20(in%20other%20words%20nothing%20happens).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20only%20happens%20on%20existing%20workbooks%20that%20I%20have.%20%26nbsp%3BIf%20I%20create%20new%20workbooks%2C%20I%20am%20still%20able%20to%20create%20such%20formulas%20by%20cliking%20on%20the%20refernce%20cell%20in%20the%20external%20worksheet.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20help%20is%20greatly%20appreciated.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-61262%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20%26amp%3B%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ENeed%20Help%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-62605%22%20slang%3D%22en-US%22%3ERe%3A%20Formulas%20with%20external%20cell%20references%20not%20working%60%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-62605%22%20slang%3D%22en-US%22%3E%3CP%3EWhat%20Excel%20version%20do%20you%20use%3F%3C%2FP%3E%3CP%3EIf%20Excel%202010%20or%202007%2C%20maybe%20you%20have%20opened%202%20instances%20of%20the%20program.%20Or%20in%20other%20words%3A%20have%20you%20opened%20Excel%20twice%3F%20You%20cannot%20reference%20to%20cells%20in%20a%20worksheet%20that%20is%20opened%20in%20another%20instance.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-61410%22%20slang%3D%22en-US%22%3ERe%3A%20Formulas%20with%20external%20cell%20references%20not%20working%60%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-61410%22%20slang%3D%22en-US%22%3E%3CP%3ELong%20shot%3A%20Could%20it%20be%20that%20the%20worksheets%20are%20grouped%20or%20that%20the%20worksheet%20is%20protected%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-61397%22%20slang%3D%22en-US%22%3ERe%3A%20Formulas%20with%20external%20cell%20references%20not%20working%60%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-61397%22%20slang%3D%22en-US%22%3E%3CP%3EIf%20you%20start%20Excel%20in%20safe%20mode%20(hold%20down%20the%20control%20key%20while%20starting%20Excel)%2C%20does%20it%20still%20fail%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-61360%22%20slang%3D%22en-US%22%3ERe%3A%20Formulas%20with%20external%20cell%20references%20not%20working%60%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-61360%22%20slang%3D%22en-US%22%3E%3CP%3EDetlef%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThey%20do%20not%20appear%20to%20be%20merged%20cells.%20%26nbsp%3BJust%20to%20be%20sure%2C%20I%20selected%20each%20worksheet%20and%20clicked%20on%20Unmerge%20Cells.%20%26nbsp%3BThe%20problem%20remains.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EScott%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-61301%22%20slang%3D%22en-US%22%3ERe%3A%20Formulas%20with%20external%20cell%20references%20not%20working%60%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-61301%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20Scott%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAre%20the%20cells%20in%20the%20external%20workbook%20merged%20cells%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2518223%22%20slang%3D%22en-US%22%3ERe%3A%20Formulas%20with%20external%20cell%20references%20not%20working%60%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2518223%22%20slang%3D%22en-US%22%3EHi%20Scott%3CBR%20%2F%3E%3CBR%20%2F%3EHave%20you%20solved%20this%20issue%3F%20I%20have%20the%20same%20problem.%3CBR%20%2F%3EDo%20you%20by%20any%20chance%20use%20any%20Towers%20Watson%20software%20on%20your%20machine%3F%3C%2FLINGO-BODY%3E
New Contributor

Something has recently happened where I am unable to create formulas in one worksheet that reference cells in another worksheet unless I manually type the cell reference.  If I try to create those formulas with external cell references by clicking in the reference cell in the external worksheet, some other random cell (or set of cells) is highlighted in the external worksheet and when I press Enter to complete the formula, it just returns a blank cell (in other words nothing happens).

 

This only happens on existing workbooks that I have.  If I create new workbooks, I am still able to create such formulas by cliking on the refernce cell in the external worksheet.

 

Any help is greatly appreciated.

6 Replies

Hello Scott

 

Are the cells in the external workbook merged cells?

 

Detlef,

 

They do not appear to be merged cells.  Just to be sure, I selected each worksheet and clicked on Unmerge Cells.  The problem remains.

 

Scott

If you start Excel in safe mode (hold down the control key while starting Excel), does it still fail?

Long shot: Could it be that the worksheets are grouped or that the worksheet is protected?

 

What Excel version do you use?

If Excel 2010 or 2007, maybe you have opened 2 instances of the program. Or in other words: have you opened Excel twice? You cannot reference to cells in a worksheet that is opened in another instance.

Hi Scott

Have you solved this issue? I have the same problem.
Do you by any chance use any Towers Watson software on your machine?